Checking Access Date Locale 1

[LizardKingSchwing]

Is there a way to tell which date format that Access 97 uses -

I have a DB with tables of data spanning around a year. I then run a load of queries on the data to build up a seed table for a few charts. However I am getting back spurious data from the queries , on dates which I assumed would have no data returned . i.e the last date entered or latest date in DB is 19/08/2002 however if i do a search on 02/09/2002 there is data returned. thus I was thinking the DB may be using the US format -

I have the regional settings setup and the timezone.

Any help guidance etc. here would be grrreat

Cheers

LK--<

 

[hermanlaksko]

Select * FROM YrTable Where Dat=&quot; & format(Date,&quot;dd-mm-yyyy&quot
This should work but some times you will have to do this to get it to work.

Dim Dato
dato= format(date,&quot;dd-mm-yyyy&quot
Select * FROM YrTable Where Dat=&quot; & dato

 

[LizardKingSchwing]

Below is a copy of the SQL statement

SELECT CallLocations.CallDir as Direction, Count(CallLocations.Interface) AS ISDNDailyCalls , Sum(CallLocations.Duration) As ISDNDailyLength FROM CallLocations WHERE CallLocations.Type = 3 and ( CallLocations.LocalRouterName = '10.252.1.11' or CallLocations.LocalRouterName = '10.252.1.10' or CallLocations.LocalRouterName = '10.20.1.248' or CallLocations.LocalRouterName = '10.20.1.247' ) and CallLocations.Interface Like 'Bri*' or CallLocations.Interface Like 'Ser*' and CallLocations.StartTime between #23:00:00 02/09/2002# AND #00:00:00 02/09/2002# group by CallLocations.CallDir

And the returned values

Direction ISDNDailyCalls ISDNDailyLength
? 267 94707
I 93 14760

Now as far as I can see the CallLocations table has no data for specified timeframe -

However if I put in date as 09/02/2002 where there is data then I get a blank chart therefore I am thinking that access is using the wrong locale i.e MM/DD/YYYY instead of DD/MM/YYYY

hmmm any other ideas...


LK--<

 

[hermanlaksko]

This should work for you:

Dim Dato, Dato2
dato= format(date,&quot;mm-dd-yyyy&quot
dato2=&quot;00:00:00 &quot; & dato
Dato=&quot;23:00:00 &quot; & dato

SELECT CallLocations.CallDir as Direction, Count(CallLocations.Interface) AS ISDNDailyCalls , Sum(CallLocations.Duration) As ISDNDailyLength FROM CallLocations WHERE CallLocations.Type = 3 and ( CallLocations.LocalRouterName = '10.252.1.11' or CallLocations.LocalRouterName = '10.252.1.10' or CallLocations.LocalRouterName = '10.20.1.248' or CallLocations.LocalRouterName = '10.20.1.247' ) and CallLocations.Interface Like 'Bri*' or CallLocations.Interface Like 'Ser*' and CallLocations.StartTime between #Dato# AND #Dato2# group by CallLocations.CallDir

 

[cheerio]

In effect in an Access SQL statement the # symbol is an instruction to take the string up to the next # and convert it into a date/time using US date conventions. Your problem is nothing to do with the locale setting of the machine. However, if you convert the SQL back to Access Query design view the same date will then be presented according to locale.

If you are not comfortable with this you can go down one of two routes.

First, as indicated by an earlier reply you can use the FORMAT function to fix the formatting as you want. For example to find everyone with my birthday I could have:

WHERE Format(birthday,&quot;mmdd&quot = &quot;0324&quot;

Alternatively, you could use Access date/time functions:

WHERE Month(birthday)=3 and Day(birthday) =24

Ken

 

[danvlas]

To evaluate the date correctly in SQL, you need the US format (month/day/year). You can use the locale in the QBE grid of a query, but switch to SQL view and you'll see it converted.

Use the Format function:

Instead of any bare DateField, use:
Format(DateField, &quot;mm/dd/yyyy[ hh:nn:ss]&quot
(Remove the time part if you don't need it.)

This will work in all cases, because the Format function actually converts the locale date to an SQL-compliant date, no matter how the Regional Settings are set o the machine.

Good luck and enjoy Dates like everybody else

Dan

 

[LizardKingSchwing]

This is how I build the query in the VBA code

sSQLin = &quot;SELECT CallLocations.CallDir as Direction, Count(CallLocations.Interface) AS ISDNDailyCalls , Sum(CallLocations.Duration) As ISDNDailyLength FROM CallLocations &quot;
sSQLin = sSQLin & &quot;WHERE (((CallLocations.Interface) Like 'Serial*') Or ((CallLocations.Interface) Like 'Bri*')) &quot;
'sSQLin = sSQLin & &quot; and CallLocations.StartTime like '&quot; & dToday & &quot; &quot; & iPrefix & iHour & &quot;*'&quot;
If (iHour = 23) Then
sSQLin = sSQLin & &quot; and CallLocations.StartTime between #&quot; & TimeSerial(iHour, 0, 0) & &quot; &quot; & dToday & &quot;# AND #&quot; & TimeSerial(0, 0, 0) & &quot; &quot; & dToday & &quot;#&quot;
Else
sSQLin = sSQLin & &quot; and CallLocations.StartTime between #&quot; & TimeSerial(iHour, 0, 0) & &quot; &quot; & dToday & &quot;# AND #&quot; & TimeSerial(iHour + 1, 0, 0) & &quot; &quot; & dToday & &quot;#&quot;
End If
sSQLin = sSQLin & &quot; and CallLocations.Type = 3 &quot; & sSQLclocks & &quot; &quot; & sqlCountry & &quot; &quot;
sSQLin = sSQLin & &quot; group by CallLocations.CallDir&quot;


iHour is a counter up until 24 i.e for each hour of day

dToday holds the date value in format dd/mm/yyyy

I tried to do a format(dToday,'mm-dd-yyyy') as requested and it worked thanks to both you guys for the help and info.
(esp. hermanlaksko)

Cheers

LK--<