Date problem

[krys]

Hello!

A quick question about dates...

In a rate_date field, I store the value '1/8/2004' as in 1st August 2004 (well... at least that's what I thought it was storing). When I write a simple select query like...

select pay_rate, rate_date from PayRates
where pay_rate = 'marking' and
rate_date = #1/8/04#; -> this doesn't work... if I put in #8/1/04# it will work. This is causing me strife in my VB code...

Is there any way I can force it to look it up as 1/8/04? (in VB)?

Thank you!
Krys

 

[Jon K]

By default, Access treats any dates surrounded by the # signs as in US date format.

On systems using d/m/yyyy format, the # signs should be avoided e.g.

SELECT pay_rate, rate_date
FROM PayRates
WHERE pay_rate = 'marking' and
rate_date = DateValue('1/8/04');
.

 

[RoyVidar]

In most cases we see dates in the format chosen in the regional settings. The exception is when we are dealing with the Jet engine by passing the sql as a string for evaluation and execution.

What is stored, is 1st August 2004, or really, the number representing that date, since dates are really stored and treated as numbers internally only displayed (formatted) as dates. In the immediate pane, try:

? format(#2004-08-01#,0)
? format(38200, "yyyy-mm-dd")

For Jet to reliably detect the correct date, you'll need to format it to an unambiguos format, which often translates to the US style (mm/dd/yyyy) or for instance "yyyy-mm-dd". So, whenever passing dates in VBA sql strings, format. In your sample, using a date litteral, simply use

#8/1/04#

The more interesting part using critaria in queries, would be to fetch the criterion from form controls or variables. For instance

dim strsql as string
strsql = "select pay_rate, rate_date from PayRates " & _
           "where pay_rate = 'marking' and " & _
           "rate_date = #" & format$(me!txtMyDate.value, "yyyy-mm-dd") & "#"
' then open the recordset or whatever

Have a look at
http://www.mvps.org/access/datetime/date0005.htm
http://allenbrowne.com/ser-36.html
for more info.

 

[EMP]

There are many ways to do it.

http://www.access-programmers.co.uk/forums/showthread.php?t=77354&highlight=systems

.

 

[krys]

cannot find Project/Library

Sorry everyone, thank you so much for your help. I'm very new at VB so i'm not entirely sure what to do... I've tried using the Format function but it highlights 'format' and says 'cannot find Project/Library'.. when I look at the help menu, it says to look at the 'references' dialogue box (under Tools) - but it won't let me...

Any ideas?

Thank you!
Krys

 

[krys]

ok... I can get to references... and from the list of available references... what do I choose?

Thanks!

 

[RoyVidar]

Yup - there's probably something fishy with the references.

See if any of them is marked as missing, invalid or something to that effect. If so, deselct (and perhaps reselect) it.

Wouldn't do much harm to deselect and reselect the Microsoft DAO Object Library either.

 

[krys]

missing reference

The missing reference is Microsoft Office XP Web Components... I just deselected it and it seeems to work fine!!

Thank you everyone for your help! Much appreciated!

Krys

 

[bogdantc]

I want to thank you everyone too! I was needing a selection criteria by date and nothing was working! Thanks!!!