Query Dates

[Always_Learning]

Hi There,

Can someone help please.

I have taken over a database and I cannot change the table structures.
The backend database is MS Sql Server.

I need to build a couple of queries but cannot get them to work.

In a table I have 2 fields EntryDate & EntryTime both are datetime.
The contents in the database for EntryDate is something like "2013-12-04 00:00:00.000" and for EntryTime it's "2000-01-01 08:40:00.000"
As you can see the dates are in one field and the time is in the other. This is causing me quite a few issues.
The 2 queries I need to build are:
Show all records entered in the last 24 hours.
So if the time is now 14:00 show me all records that have been entered from yesterday at 14:00 to 14:00 today.

I also need to be able to show what has been entered from yesterday at 22:00 to today at 13:00

I know a few of you will think this is simple but it's had me pulling my hair out.

Thanks for any help you can give I really appreciate it.

Best Regards,

 

[plog]

Just because data is stored incorrectly doesn't mean you have to work with it incorrectly. You shouldn't be running queries on those tables. Instead you need to create a query that holds the data correctly, then whenever you need data, you use that query as the basis for other queries.

So, create that query. There are functions in access (http://www.techonthenet.com/access/functions/date/datevalue.php) to remove the date portion of a field, there are functions (http://www.techonthenet.com/access/functions/date/timevalue.php) to remove the time portion of a field, there are also functions (http://www.techonthenet.com/access/functions/datatype/cdate.php) to create a DateTime field based on Date and Time values. Use those functions to create a field that generates the correct data you need. Then build a query based on that query to generate the actual data you need.

 

[Brianwarnock]

I don't think that the CDate function is required here just

Mydatetime = Datevalue(fielddate)+ Timevalue(fieldtime)

Brian

 

[Always_Learning]

Hi There,

When I try Mydatetime = Datevalue(fielddate)+ Timevalue(fieldtime) in the query builder without a where clause the column gets filled with the correct data but when I add a where clause, a box comes up asking me for a value for Mydatetime.

I'm obviously not understanding what I should do.

I want to query the db to get any record entered in the last 24 hours. How would I use the Mydatetime column to query the last 24 hours.

Thanks for your help on this I appreciate it.

Best Regards,

 

[Brianwarnock]

As you now have a column of datetime you can simply query that.

Being a bit more specific and using your field names in the design grid you would have
In the field row
Entrydatetime: DateValue(entrydate) + Timevalue(entrytime)
In the criteria something like
Between Date()-1 + #14:00# And Date() + #14:00#

Sorry I can't test haven't got Access at the moment

Brian

 

[Always_Learning]

Hi There,

Sorry, I can't get it to work.

All the best,

 

[Brianwarnock]

You don't say what does or does not happen, I will assume my formula doesn't work so either hard code the date time in the criteria and/or try using dateadd

Between Dateadd("h",14,Date()-1) and Dateadd("h",14,Date())

Brian

 

[Always_Learning]

You don't say what does or does not happen, I will assume my formula doesn't work so either hard code the date time in the criteria and/or try using dateadd

Between Dateadd("h",14,Date()-1) and Dateadd("h",14,Date())

Brian

Hi Brian,

I thank you for your time and really appreciate your help.

I am adding what you suggested in the Access query builder.
I add Mydatetime = Datevalue(fielddate)+ Timevalue(fieldtime) in the field part but if I add a where clause for MyDateTime, when I run the query a box pops up asking for the value of MyDateTime.

All the best,

 

[namliam]

In a query if you want to calculate this, try:
Mydatetime: Datevalue(fielddate)+ Timevalue(fieldtime)

 

[Always_Learning]

Hi There,

I get a data type mismatch when I put something in the criteria field.
Please see screenshot attached.


Best Regards,

 

[namliam]

That is because your data is flawed, if there is a row that has NULL values in one of the two fields that will happen.

Odd error, but still that is it

 

[Always_Learning]

That is because your data is flawed, if there is a row that has NULL values in one of the two fields that will happen.

Odd error, but still that is it

Thanks for the reply,

Is there any way of getting over that?

Best Regards,

 

[CazB]

You are most probably getting the error because one or other of the two fields (yourdate and yourtime) does not contain a value... or does not contain a date/time. Check that by looking for nulls in those fields.

What would you want it to do in cases where there were nulls?

 

[Always_Learning]

Ok got this working now.

Set all Null's to 0

Thanks everyone for your help.

Best Regards,