Date Filter (1 Viewer)

NSAMSA

Registered User.
Local time
Today, 00:55
Joined
Mar 23, 2014
Messages
66
All:

I'm trying to filter a query with the between function, however there's something wrong with the date format that makes its implementation bring up the following error:

"This expression is typed incorrectly, or it is too complex to be evaluated."

I had to derive the date from a longer string. My ODBC connection table returned an entry formatted as "yyyy-mm-dd hh:mm:sss - 05:00"

In order to get the date, I used the left function, format function and CDate function:

CDate(Format(Left(dtoDateConsumed, 10), "mm/dd/yyyy"))

I then put this entry into another query and in the criteria section I have:

Between [start date] and [end date]

How do I make this query work?

Thank you in advance for any help.
 

isladogs

MVP / VIP
Local time
Today, 07:55
Joined
Jan 14, 2017
Messages
18,186
I've just done something very similar to import dates like this: 2017-11-17T00:00:00.000Z as 17/11/2017. However I used a recordset

For the query SQL you don't need CDate & Format
In fact you don't need to format at all as the query designer handles that info natively

In the query designer criteria for the date field, just put:
Code:
"Between #" & [StartDate] & "# And #" & [EndDate] & "#"

NOTE: If you want to do this in VBA, a different solution is needed
 

NSAMSA

Registered User.
Local time
Today, 00:55
Joined
Mar 23, 2014
Messages
66
I tried that different criteria design, but to no avail. I'm still getting the same error message.
 

isladogs

MVP / VIP
Local time
Today, 07:55
Joined
Jan 14, 2017
Messages
18,186
Hang on - are StartDate & EndDate fields or variables?

Also post your query SQL & a typical pair of dates
 
Last edited:

NSAMSA

Registered User.
Local time
Today, 00:55
Joined
Mar 23, 2014
Messages
66
SELECT dbo_tblConsumption.dtoDateConsumed, dbo_tblConsumption.SiteId, Right([dtoDateConsumed],15) AS Expr1, Left([Expr1],8) AS ConTime, CDate(Format(Left([dtoDateConsumed],10),"mm/dd/yyyy")) AS ConDate
FROM dbo_tblConsumption INNER JOIN dbo_tblPallet ON dbo_tblConsumption.PalletId = dbo_tblPallet.PalletID
WHERE (((dbo_tblConsumption.SiteId)=58) AND ((CDate(Format(Left([dtoDateConsumed],10),"mm/dd/yyyy"))) Between [start date] And [end date]));

Typically I would just want information for one day, say 11/15/17. However, I'll also check on a weekly basis as well.
 

isladogs

MVP / VIP
Local time
Today, 07:55
Joined
Jan 14, 2017
Messages
18,186
Hi

You didn't answer my question
are StartDate & EndDate fields or variables?
I need to know where those values are chosen.
For example, are they textboxes on a form?

Anyway there are several things wrong with your query
It will be quicker for me to fix it if you can post a cut down version of your db
with the 2 tables concerned & the query
I'll explain what was wrong when I reply
 

NSAMSA

Registered User.
Local time
Today, 00:55
Joined
Mar 23, 2014
Messages
66
Sorry, start date and end date are variables, not fields.

How do I attach a db to this for you to look at?
 

NSAMSA

Registered User.
Local time
Today, 00:55
Joined
Mar 23, 2014
Messages
66
Here's a dummy version of the database. One thing that's important to understand is that the dbo tables are ODBC connections which means the data type cannot be altered. Also, I parsed the data down to only 11/16 and 11/17 as there was too much overall.

Thank you!
 

Attachments

  • DbTest.accdb
    1.8 MB · Views: 81

isladogs

MVP / VIP
Local time
Today, 07:55
Joined
Jan 14, 2017
Messages
18,186
The 3 queries you supplied need a table called tblMaterial which you didn't supply

Shall I ignore that & just adapt what you posted or do you want to repost with the missing table?

If reposting, you might need to zip to get around the 2MB limit
 

NSAMSA

Registered User.
Local time
Today, 00:55
Joined
Mar 23, 2014
Messages
66
Sorry, you can ignore that. The truly important this is getting the consumption date to act like a "date". Once again thank you for the help.
 

isladogs

MVP / VIP
Local time
Today, 07:55
Joined
Jan 14, 2017
Messages
18,186
Here you go ....

Code:
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT dbo_tblConsumption.dtoDateConsumed, dbo_tblConsumption.SiteId, Left(Right([dtoDateConsumed],15),8) AS ConTime, CDate(Left([dtoDateConsumed],10)) AS ConDate
FROM dbo_tblConsumption INNER JOIN dbo_tblPallet ON dbo_tblConsumption.PalletId = dbo_tblPallet.PalletID
WHERE (((dbo_tblConsumption.dtoDateConsumed)<>'') AND ((dbo_tblConsumption.SiteId)=58) AND ((CDate(Left([dtoDateConsumed],10))) Between [Start Date] And [End Date]));

and here are the results:



I'm in the UK so my default date format is dd/mm/yyyy
If your default format is different, it will automatically match that

Query saved as qryDateFilter

Changes made
1. Added parameters to the query
If you are getting the start & end date from a form, remove the PARAMETERS line
2. I've combined the 2 fields used to get ConTime.
I recommend you don't create an Expr1 & then use it in another field.
It doesn't always work if its too complex.
If it does work it slows down query results.
3. I've added a condition dtoDateConsumed)<>'' as you had a null value for 1 record
4. Notice how much simpler the date expressions are

Also there is no need to preface Access tables from e.g. SQL Server with 'dbo_'
Its just adding unnecessary complexity.
So you could have e.g. tblConsumption & tblPallet
 

Attachments

  • Capture.PNG
    Capture.PNG
    11 KB · Views: 236
  • DbTest.zip
    168.4 KB · Views: 54

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
19,169
the Criteria for your Query would look something
like this:

WHERE (((IIf(Trim([dtoDateConsumed] & "")="",Null,DateSerial(Left([dtoDateConsumed],4),Mid([dtoDateConsumed],6,2),Mid([dtoDateConsumed],9,2)))) Between CDate([start date]) And CDate([end date])));
 

Users who are viewing this thread

Top Bottom