Access Date Query Help

[jbrupert]

Hey guys,

I'm having a little trouble figuring out how to create this access query correctly. Long story short, I have a table of data with entry date fields.

What I need to do is pull all records from ((Now - 1) @ 12:00 PM) - (Now @ 12:00 PM). I understand the Now() function but I'm having trouble figuring out how to get it to go back to 12:00 PM.

I'd rather do this in a query instead of VB because my end users will not understand VBA or how to support it.

The criteria I'm currently using has hard coded dates but I need this to be dynamic:

Between #5/14/2015 12:00:00 PM# And #5/15/2015 12:00:00 PM#

Thanks,
Jon

 

[marlan]

Hi,
First - Now function returns the current date and time. Use Date function to receive the current date without time.
Next - look into DateAdd function (Google: DateAdd VBA) to Add or subtract time from a date and-or time, here is a good place to start: http://www.techonthenet.com/access/functions/date/dateadd.php.

Good luck!

 

[nfk]

I dont get it:

this wont do it?:

SELECT
    *
    FROM Records
    WHERE DateCreated>='@dt_from' AND DateCreated<='@dt_to'

Where clearly you first define your variables in the access way. eg: iF!"['!'"]((IF![!(ElseIf(With![]"&'''(-)'''@('1'="1"))&))]""!

 

[CJ_London]

@nfk

If you are going to offer a potential solution to an OP, suggest you do so with an answer relevant to the OP's environment - or at least state which environment you are using

e.g.

in dbase I use....

As for 'iF!"['!'"]((IF![!(ElseIf(With![]"&'''(-)'''@('1'="1"))&))]""! ' I think this is just a reflection of the way your mind works. The only thing missing are the colors red and green

 

[nfk]

@nfk

If you are going to offer a potential solution to an OP, suggest you do so with an answer relevant to the OP's environment - or at least state which environment you are using

e.g.

in dbase I use....

As for 'iF!"['!'"]((IF![!(ElseIf(With![]"&'''(-)'''@('1'="1"))&))]""! ' I think this is just a reflection of the way your mind works. The only thing missing are the colors red and green

Attacks, attacks, constant attacks against my person... I asked if that minimal query wont do the job.

I believe in Access, depending if you're working with mdb or adp format you can send 'variables' to a query by replacing my @dt_from and @dt_to with [DateFrom], [DateTo] sort of input.

And I guess you can have a Calendar object that will populate the dates and if needed. I dont know what format of date were talking, maybe a CONVERT will be needed... or joining the variable holding the date (1987-03-29 [my birthday {if anyone cares}]) with a variable holding the time (00:00:00.000).

More info is missing but I think my original query should work in most cases...

 

[CJ_London]

@nfk

As I am sure you know, the question was about

I'm having trouble figuring out how to get it to go back to 12:00 PM

The OP already has the basic criteria

Between #5/14/2015 12:00:00 PM# And #5/15/2015 12:00:00 PM#

But perhaps in your world 'BETWEEN' doesn't exist so you didn't recognise it.

 

[nfk]

@nfk

As I am sure you know, the question was about



The OP already has the basic criteria


But perhaps in your world 'BETWEEN' doesn't exist so you didn't recognise it.

I say we should wait for the OP to come around and have he's say...

 

[marlan]

I say we should wait for the OP to come around and have he's say...

Hay, the guy is asking for some help with his homework, why make fun of him? just because you'v learned a few years before him?!

 

[jbrupert]

Hey guys,

Thanks for the suggestions.

marlan, I've looked into using the dateAdd function but I still can't quite figure out how to get that to point back to 12:00:00 PM from a dynamic Now() date.

nfk, Your code posted is exactly what I'm trying to do its just getting those dates correct that is posing the issue. I'm not quite sure what you mean by 'iF!"['!'"]((IF![!(ElseIf(With![]"&'''(-)'''@('1'="1"))&))]""! ' though. I like the idea of storing the date as one variable and the time as another but I don't quite understand how I would get access to add the variables together in order to create one date/time value.

I decided to cave and wrote some VB code to do this last night. I just took the same SQL statement from the query with hard-coded dates and set it equal to a string. Then I dynamically update the dates to the correct values, set it equal to a recordset and copy all of the data to a table. Not nearly as clean and more difficult to support for all my non-VBA firends here. Alas, there's only so long I'm willing to Google hunt before I give in and do it the "easy way" though.

Thanks,
Jon

 

[spikepl]

Between Date()-1 + #12:00:00 PM# AND Date() + #12:00:00 PM#

Actually:

Between Date()-0.5# AND Date() + 0.5# is the numerical equivalent of the above (where the # signifies Double)

 

[nfk]

I'm not quite sure what you mean by 'iF!"['!'"]((IF![!(ElseIf(With![]"&'''(-)'''@('1'="1"))&))]""! ' though.

Don't worry about that, thats another story...

how I would get access to add the variables together in order to create one date/time value.

 

[jbrupert]

Spikepl,

That was it, so simple I feel dumb now. Didn't know you could append the time with a simple + like that. Here's the criteria I'm using now. Seems to be working with my initial tests:

Between (Now()-1)+#12:00:00 PM# And Now()+#12:00:00 PM#

Thanks,
Jon

 

[spikepl]

Not quite. Yours differs from mine because you use Now() , and therefore it depends on what time of day it is run.

Now() gives you date AND time, and #12:00:00 PM# has the numerical value of 0.5 (A half day) . If you run your stuff at 9 PM you'll get data from 9 AM today until 9 AM tomorrow. Is that what you wanted?

 

[CJ_London]

think you need to use Date() - per Spikes post - rather than Now()

if Now() is 09:57:00 AM then adding #12:00:00 PM# will make it 09:57:00 PM

Note that a date value is actually numeric (a double) - the number before the decimal point is the day (today is 42150) and the number after the decimal point is a fraction expressing time as a percentage of the number of seconds in 24 hours - so 9am is 0.25, midday 0.5 and 5pm is 0.208333

So Spike's formula could be written as

between Date()-0.5 and Date()+0.5

 

[jbrupert]

I see...

So if I wanted my criteria to be between yesterday 12:00PM and today 12:00 PM I should use something along the lines of:

Between (Date()-0.5) And (Date()+0.5)

That's producing an error:

[Oracle][ODBC][Ora]ORA-01847: day of month must be between 1 and last day of month (#1847)

Thanks,
Jon :banghead:

 

[jbrupert]

Got it...

Had to do some conversions but I think this formula ultimately works:

Between
CDate(CStr(Month(Now()))+"/"+CStr((Day(Now())-1))+"/"+CStr(Year(Now()))+" 12:00:00 PM")
And
CDate(CStr(Month(Now()))+"/"+CStr((Day(Now())))+"/"+CStr(Year(Now()))+" 12:00:00 PM")


Thanks,
Jon

 

[marlan]

Hi,

The Date() function returns the current date at 00:00:00. 12:00 PM is 12 hours away,
This is exactly where DateAdd Comes handy:

Between DateAdd ( "h", -12, date() ) and DateAdd ( "h", 12, date() )

ATB

 

[marlan]

BTW, are you using a pass-through Query to Oracle?
DateAdd is a Microsoft function. This may not work...

 

[jbrupert]

Yeah its an access query on top of an Oracle database. I've never tried the DateAdd function successfully but the query I posted using now() is working as expected. It's just a longer implementation of the same criteria.

Either way, thanks for all the help on this one. It's definitely been useful.

Jon