Unable to use today's date as a criteria in a query

Question

Ok, been a good decade since I've worked with Access and SQL, so a little forgiveness please.

Short version - when I add the query criteria to only display rows which match today's date, the query returns no rows; despite there indeed being matching rows there.

I have a sneaking suspicion that [FG Orders by Date].[FG Date] is somehow somewhere being treated as text, because after looking at the sort results of a query, it isn't sorting the field properly regardless of sort order.

Hope the following information is enough for someone to help me understand where I've gone wrong.

Problem being addressed

The query, and report coming from it, is a Kitchen Production Report that shows the Component Goods that need to be produced for the Finished Goods they have to dispatch that day.

There's a complexity of nicely laid out tables which manage things nicely, which will become apparent from the SQL below.

Due to the workflow, this report only ever needs to show the information for the current day.

Summary

I have a query, where the date column [FG Orders by Date].[FG Date], which is formatted as a Short Date (DD/MM/YYYY).

I've tested it's correctly formatted by creating an Expression column to transform it into a different format, which works perfectly.

Rows are returned by the query without an issue.

Whenever I add criteria to the query to only return rows for today's date (13/06/2019) - using Date(), the query returns no rows despite there being 11 rows with that date value.

Remove the criteria agaun, and everything comes back nicely.

SQL

This is the query code without the criteria added:

SELECT [FG Orders by Date].FGOrderID, 
       [FG Orders by Date].[FG Date], 
       [FG Orders by Date].[FG Date] AS ExpTodaysDate, 
       [FG Orders by Date].FGSKU, 
       [Finished Goods].[FG Name], 
       [FG to CG mapping].CGSKU, 
       [Component Goods].[CG Category], 
       [CG Category].[CG Category Name], 
       [Component Goods].[CG Name], 
       [Component Goods].[CG Unit], 
       Units.[Unit name], 
       [Component Goods].[CG Unit value], 
       [FG Orders by Date].[FGSKU Quantity], 
       [FG to CG mapping].[CG Qty Required for FGSKU], 
       [FGSKU Quantity]*[CG Qty Required for FGSKU] AS TotalQtyOfCGSKU
FROM (Units INNER JOIN ([CG Category] INNER JOIN [Component Goods] ON [CG Category].[CG Category ID] = [Component Goods].[CG Category]) ON Units.[Unit Symbol] = [Component Goods].[CG Unit]) INNER JOIN (([Finished Goods] INNER JOIN [FG Orders by Date] ON [Finished Goods].[FGSKU] = [FG Orders by Date].[FGSKU]) INNER JOIN [FG to CG mapping] ON [Finished Goods].[FGSKU] = [FG to CG mapping].[FGSKU]) ON [Component Goods].[CGSKU] = [FG to CG mapping].[CGSKU];

And with the criteria added:

SELECT [FG Orders by Date].FGOrderID, 
       [FG Orders by Date].[FG Date], 
       [FG Orders by Date].[FG Date] AS ExpTodaysDate, 
       [FG Orders by Date].FGSKU, 
       [Finished Goods].[FG Name], 
       [FG to CG mapping].CGSKU, 
       [Component Goods].[CG Category], 
       [CG Category].[CG Category Name], 
       [Component Goods].[CG Name], 
       [Component Goods].[CG Unit], 
       Units.[Unit name], 
       [Component Goods].[CG Unit value], 
       [FG Orders by Date].[FGSKU Quantity], 
       [FG to CG mapping].[CG Qty Required for FGSKU], 
       [FGSKU Quantity]*[CG Qty Required for FGSKU] AS TotalQtyOfCGSKU
FROM (Units INNER JOIN ([CG Category] INNER JOIN [Component Goods] ON [CG Category].[CG Category ID] = [Component Goods].[CG Category]) ON Units.[Unit Symbol] = [Component Goods].[CG Unit]) INNER JOIN (([Finished Goods] INNER JOIN [FG Orders by Date] ON [Finished Goods].[FGSKU] = [FG Orders by Date].[FGSKU]) INNER JOIN [FG to CG mapping] ON [Finished Goods].[FGSKU] = [FG to CG mapping].[FGSKU]) ON [Component Goods].[CGSKU] = [FG to CG mapping].[CGSKU]
WHERE ((([FG Orders by Date].[FG Date])=Date()));

SQL Create statements for relevant tables

(This is based on an export using DBWScript)

CREATE TABLE [CG Category] (
    [CG Category ID] AUTOINCREMENT,
    [CG Category Name] TEXT(255) WITH COMPRESSION,
    CONSTRAINT [ID] PRIMARY KEY ([CG Category ID])
);
ALTER TABLE [CG Category] ALLOW ZERO LENGTH [CG Category Name];
ALTER TABLE [CG Category] FORMAT [CG Category Name] SET "@";
CREATE TABLE [Component Goods] (
    [CGSKU] DOUBLE NOT NULL,
    [CG Name] TEXT(255) WITH COMPRESSION,
    [CG Unit] TEXT(255) WITH COMPRESSION NOT NULL,
    [CG Unit value] DOUBLE,
    [CG Category] LONG,
    [CG Pick Location] TEXT(255) WITH COMPRESSION,
    CONSTRAINT [CGSKU] PRIMARY KEY ([CGSKU])
);
ALTER TABLE [Component Goods] ALLOW ZERO LENGTH [CG Name];
ALTER TABLE [Component Goods] ALLOW ZERO LENGTH [CG Unit];
ALTER TABLE [Component Goods] ALLOW ZERO LENGTH [CG Pick Location];
ALTER TABLE [Component Goods] FORMAT [CGSKU] SET "General Number";
ALTER TABLE [Component Goods] FORMAT [CG Name] SET "@";
ALTER TABLE [Component Goods] FORMAT [CG Unit value] SET "General Number";
ALTER TABLE [Component Goods] FORMAT [CG Pick Location] SET "@";
CREATE TABLE [FG Category] (
    [CG Category ID] AUTOINCREMENT,
    [CG Category Name] TEXT(255) WITH COMPRESSION,
    CONSTRAINT [ID] PRIMARY KEY ([CG Category ID])
);
ALTER TABLE [FG Category] ALLOW ZERO LENGTH [CG Category Name];
ALTER TABLE [FG Category] FORMAT [CG Category Name] SET "@";
CREATE TABLE [FG Orders by Date] (
    [FGOrderID] AUTOINCREMENT,
    [FG Date] DATETIME DEFAULT Now(),
    [FGSKU] DOUBLE,
    [FGSKU Quantity] LONG DEFAULT 0,
    CONSTRAINT [PrimaryKey] PRIMARY KEY ([FGOrderID])
);
ALTER TABLE [FG Orders by Date] FORMAT [FG Date] SET "Short Date";
ALTER TABLE [FG Orders by Date] DECIMAL PLACES [FGSKU Quantity] SET 0;
ALTER TABLE [FG Orders by Date] FORMAT [FGSKU Quantity] SET "General Number";
CREATE TABLE [FG to CG mapping] (
    [FGCG ID] AUTOINCREMENT,
    [FGSKU] DOUBLE,
    [CGSKU] DOUBLE,
    [CG Qty Required for FGSKU] DOUBLE,
    CONSTRAINT [ID] PRIMARY KEY ([FGCG ID])
);
ALTER TABLE [FG to CG mapping] FORMAT [FGSKU] SET "General Number";
ALTER TABLE [FG to CG mapping] FORMAT [CGSKU] SET "General Number";
ALTER TABLE [FG to CG mapping] FORMAT [CG Qty Required for FGSKU] SET "General Number";
CREATE TABLE [Finished Goods] (
    [FGSKU] DOUBLE NOT NULL,
    [FG Name] TEXT(255) WITH COMPRESSION,
    [FG Category] LONG,
    [FG Category Old] TEXT(255) WITH COMPRESSION,
    [FG Pick Location] TEXT(255) WITH COMPRESSION,
    [FG Handle in System] BIT NOT NULL DEFAULT =Yes,
    CONSTRAINT [FGSKU] PRIMARY KEY ([FGSKU])
);
ALTER TABLE [Finished Goods] ALLOW ZERO LENGTH [FG Name];
ALTER TABLE [Finished Goods] ALLOW ZERO LENGTH [FG Category Old];
ALTER TABLE [Finished Goods] ALLOW ZERO LENGTH [FG Pick Location];
ALTER TABLE [Finished Goods] FORMAT [FGSKU] SET "General Number";
ALTER TABLE [Finished Goods] FORMAT [FG Name] SET "@";
ALTER TABLE [Finished Goods] FORMAT [FG Category Old] SET "@";
ALTER TABLE [Finished Goods] FORMAT [FG Pick Location] SET "@";
ALTER TABLE [Finished Goods] FORMAT [FG Handle in System] SET "Yes/No";
CREATE TABLE [Units] (
    [Unit ID] AUTOINCREMENT,
    [Unit name] TEXT(255) WITH COMPRESSION,
    [Unit Symbol] TEXT(255) WITH COMPRESSION,
    CONSTRAINT [PrimaryKey] PRIMARY KEY ([Unit ID])
);
ALTER TABLE [Units] ALLOW ZERO LENGTH [Unit name];
ALTER TABLE [Units] ALLOW ZERO LENGTH [Unit Symbol];
ALTER TABLE [Units] FORMAT [Unit name] SET "@";
ALTER TABLE [Units] FORMAT [Unit Symbol] SET "@";
CREATE INDEX [CG Category]
    ON [Component Goods] ([CG Category]);
CREATE INDEX [CG Unit]
    ON [Component Goods] ([CG Unit]);
CREATE INDEX [FGSKU]
    ON [FG Orders by Date] ([FGSKU]);
CREATE INDEX [FG to CG mappingCGSKU]
    ON [FG to CG mapping] ([CGSKU]);
CREATE INDEX [FG to CG mappingFGSKU]
    ON [FG to CG mapping] ([FGSKU]);
CREATE INDEX [FG Category]
    ON [Finished Goods] ([FG Category]);
CREATE INDEX [Unit ID]
    ON [Units] ([Unit ID]);
CREATE INDEX [Unit Symbol]
    ON [Units] ([Unit Symbol]);
ALTER TABLE [Component Goods]
    ADD CONSTRAINT [CG CategoryComponent Goods]
    FOREIGN KEY NO INDEX ([CG Category]) REFERENCES
        [CG Category] ([CG Category ID]);
ALTER TABLE [Component Goods]
    ADD CONSTRAINT [UnitsComponent Goods]
    FOREIGN KEY NO INDEX ([CG Unit]) REFERENCES
        [Units] ([Unit Symbol]);
ALTER TABLE [FG Orders by Date]
    ADD CONSTRAINT [Finished GoodsFG Orders by Date]
    FOREIGN KEY NO INDEX ([FGSKU]) REFERENCES
        [Finished Goods] ([FGSKU]);
ALTER TABLE [FG to CG mapping]
    ADD CONSTRAINT [Component GoodsFG to CG mapping]
    FOREIGN KEY NO INDEX ([CGSKU]) REFERENCES
        [Component Goods] ([CGSKU]);
ALTER TABLE [FG to CG mapping]
    ADD CONSTRAINT [Finished GoodsFG to CG mapping]
    FOREIGN KEY NO INDEX ([FGSKU]) REFERENCES
        [Finished Goods] ([FGSKU]);
ALTER TABLE [Finished Goods]
    ADD CONSTRAINT [FG CategoryFinished Goods]
    FOREIGN KEY NO INDEX ([FG Category]) REFERENCES
        [FG Category] ([CG Category ID]);

Example of query where date isn't sorting properly

(Apologies, i've tried posting this in markdown, but I can't figure out why the generated code isn't formatting properly)

Answer 1

You missed the time portion of [FG Orders by Date].[FG Date] as it is defined as

[FG Date] DATETIME DEFAULT Now()

Your table displays the data formated (only date part),

ALTER TABLE [FG Orders by Date] FORMAT [FG Date] SET "Short Date"

but in database it is stored with the time portion (open table [FG Orders by Date] and click on a [FG Date] field, it will show its true value).

Don't hide data structure (format at table level, multivalue fields, ..), the formatting could be done in the query:

SELECT Format([FG Orders by Date].[FG Date],"Short Date") As [FG Date Formatted], ... .

Datetime is stored as double value where the fraction is the time portion (elapsed time of day / whole time of day), but a date without a time is just an integer value E.g:

CDbl(#2019-06-18 00:00:00#) = 43634 = CInt(#2019-06-18#)

(0 hours / 24 hours, fraction is 0)

and:

CDbl(#2019-06-18 12:00:00#) = 43634.5 <> CInt(#2019-06-18 12:00:00#) =  43634 

(12 hours / 24 hours, fraction is .5)

If your [FG Orders by Date].[FG Date] is stored with the time portion (it hasNow()as default value, notDate()), it doesn't match your criteria=Date()as the only matchtes for a date in a datetime are the midnight datetimes.

Criteria for a whole day 2019-06-18

([FG Orders by Date].[FG Date] >= #06/18/2019# AND [FG Orders by Date].[FG Date] < #06/19/2019#)

as the datetime could be somewhere between this day and not the next day.

Criteria for todays date could be expressed as:

([FG Orders by Date].[FG Date] >= Date() AND [FG Orders by Date].[FG Date] < DateAdd("d", 1, Date())

You may want to be clever and just use the Date part of [FG Date] with:

DateValue([FG Orders by Date].[FG Date]) = Date()

This can return the same results, but you need to know that this prevents the usage of an index on [FG Orders by Date].[FG Date], what slows down the query and if [FG Orders by Date].[FG Date] Is Null this raises an error inDateValue()function.