Date/Time query

[fat controller]

I should know this, but can't seem to get my head round where I am going wrong.

I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.

I have tried

Between [StartDate:] And [EndDate:]

And

Between [StartDate:] & "00:00" And [EndDate:] & "23:59"

Neither of which work;

Can anyone advise please?

 

[KenHigg]

When you say they don't work, what do you mean? Do they not return records at all or is it not including some records you think it should, etc.,?

 

[fat controller]

No records are returned, with the error message stating that there are no records that match the date criteria entered.

This query is normally ran from a form, however I have tried making another very simple query with "Between [Start] And [End]", and this also returns no records

 

[KenHigg]

For starters, I'm thinking you need to place pound signs (#) around the prompt syntax.

Between #[StartDate:]# And #[EndDate:]#

I'm a little rusty on this. Maybe someone else can jump in...

 

[Gasman]

I would build it with the Build tool.

I've just knocked up a quick form with two dates and produced

SELECT TestTransactions.ID, TestTransactions.Date, TestTransactions.Account, TestTransactions.Num, TestTransactions.Description
FROM TestTransactions
WHERE (((TestTransactions.Date) Between [Forms]![Form1]![txtStartDate] And [Forms]![Form1]![txtEndDate]));

and I also tried the query with hard coded dates first and had to use uk format to get the data i expected.

Using UK format and your parameters

SELECT TestTransactions.ID, TestTransactions.Date, TestTransactions.Account, TestTransactions.Num, TestTransactions.Description
FROM TestTransactions
WHERE (((TestTransactions.Date) Between [StartDate] And [EndDate]));

I get the same data after entering the values from the prompt.?

 

[fat controller]

This is going to be one of those that drives me mad

I have just quickly thrown this together, and can't see why it wouldn't work - I think it pretty much echoes what you have posted Gasman

SELECT tblAuditTrail.AuditTrailID, tblAuditTrail.DateTime, tblAuditTrail.UserName, tblAuditTrail.FormName, tblAuditTrail.RecordID, tblAuditTrail.Action, tblAuditTrail.FieldName, tblAuditTrail.OldValue, tblAuditTrail.NewValue
FROM tblAuditTrail
WHERE (((tblAuditTrail.DateTime) Between [start] And [end]));

No records returned at all, yet when I look at the table I can see that they are there?

 

[leanpilar]

try this

>=#Cstr([date])# and <=#Cstr([date])

because in query between ## must be a date as string

 

[Gasman]

try this

>=#Cstr([date])# and <=#Cstr([date])

because in query between ## must be a date as string

So why does my code work without them?
It works with form fields, it works with adhoc parameters like [startdate]

I agree normally # would be used and it would also be in usa date format not uk, yet my query works fine. In fact it did not work when I used the usa format as the date in my table is uk format.

 

[Gasman]

and you are entering the dates from the prompts of the query?

Try the 'other' format merely as a test.

This is going to be one of those that drives me mad

I have just quickly thrown this together, and can't see why it wouldn't work - I think it pretty much echoes what you have posted Gasman

SELECT tblAuditTrail.AuditTrailID, tblAuditTrail.DateTime, tblAuditTrail.UserName, tblAuditTrail.FormName, tblAuditTrail.RecordID, tblAuditTrail.Action, tblAuditTrail.FieldName, tblAuditTrail.OldValue, tblAuditTrail.NewValue
FROM tblAuditTrail
WHERE (((tblAuditTrail.DateTime) Between [start] And [end]));

No records returned at all, yet when I look at the table I can see that they are there?

 

[leanpilar]

I had same problem for filter date because filter accept only #dd/mm/yyyy# but Cdate in my system return dd.mm.yyyy. and filter doesen't work so I added a calculated field in query and return month as integer and filter that field and not date...

 

[fat controller]

Nope - still not working. This is really, really odd because I know for a fact that this query and the related report have worked in the past, and I haven't changed a thing?

 

[fat controller]

Cracked it! I have no idea why, but it did not like giving data for only one date (ie, start date and end date the same), so this has resolved things:

SELECT tblAuditTrail.AuditTrailID, tblAuditTrail.DateTime, tblAuditTrail.UserName, tblAuditTrail.FormName, tblAuditTrail.RecordID, tblAuditTrail.Action, tblAuditTrail.FieldName, tblAuditTrail.OldValue, tblAuditTrail.NewValue
FROM tblAuditTrail
WHERE (((tblAuditTrail.DateTime) Between DateValue([Forms]![AuditReportSelect]![txtStartDate]) And DateValue([Forms]![AuditReportSelect]![txtEndDate]+1)) AND ((tblAuditTrail.FormName)=[forms]![AuditReportSelect]![txtFormName]));

 

[namliam]

possibly because your datetime field is a date time field?
For your query to find 19 aug 2015 15:37, you need to search for between date and date+1

Or remove the time part of the contents of your datetime field.

Mixing Date and Datetime fields can cause to unexpected results like this

 

[Brianwarnock]

possibly because your datetime field is a date time field?
For your query to find 19 aug 2015 15:37, you need to search for between date and date+1

Or remove the time part of the contents of your datetime field.

Mixing Date and Datetime fields can cause to unexpected results like this

Yes people forget that a date time field always has a time, if not quoted it defaults to 00:00:00


Brian