More date problems [swapping months!] 2

[Trancemission]

I have a procedure which when clicked should take a date, lookup some values, add a number of days to the date [depeneding on previous lookup] and loop thorugh this for as long as there is data.

My looping all works fine the problem I face is with dates. Access seems to interperate the date as mm/dd/yyyy but all my dates are uk format dd/mm/yyyy. I have tried numerous functions and trying to change the data [as you can see in comments in my code] but to no luck.

I understand that MS access 2000 does all functions using the mm/dd/yyyy format?!?!?

Anyways my code:

'Declare databases
Dim DbCurr As Database
Set DbCurr = DBEngine.Workspaces(0).Databases(0)

'Open Recordset
Dim dbrs As Recordset
Dim dbRs2 As Recordset
Dim hotelrs As Recordset
Dim total As Currency
Dim anothedate As Date
Dim sdate As Date
Dim edate As Date
Dim gdatE As Date
Dim globaldate As Date
Dim pdate As Date

' Set global dates
globaldate = #12/31/2004#

Here we set a global date where I want to start from, this will be a input box when done. I try to enter the date as UK but i'm thinking it changes this becasue I have declared this var as a date.

You can see where I have been playing
'globaldate = Format(globaldate, "mm / dd / yyyy")
'tmp = Split(globaldate, "/")
'globalukdate = "" & tmp(1) & "/" & tmp(0) & "/" & tmp(2)
'globaldate = globalukdate

Below is where I get my top level data [the is where I get the number I need to add to the date]

' Now we can attempt to get a final price
Set dbrs = DbCurr.OpenRecordset("SELECT * FROM tbl_multi_centres_detail WHERE (((tbl_multi_centres_detail.MultiCode) = '" & Me.MultiCode & "')) ORDER BY tbl_multi_centres_detail.legorder;")
MsgBox "Number of records: " & dbrs.RecordCount
dbrs.MoveFirst
'Loop thorugh and get price
sdate = globaldate
edate = globaldate + dbrs!NumNights

Do Until dbrs.EOF

Set hotelrs = DbCurr.OpenRecordset("SELECT AllPrices_ww.HName, AllPrices_ww.ExValue, AllPrices_ww.Room_Type, AllPrices_ww.PAX, AllPrices_ww.Sdate, AllPrices_ww.edate, AllPrices_ww.txtCurrency, AllPrices_ww.curCosts, AllPrices_ww.HtCode, AllPrices_ww.MealTxt FROM AllPrices_ww WHERE (((AllPrices_ww.Room_Type)='" & dbrs![HotelRoomType] & "') AND ((AllPrices_ww.PAX)='" & dbrs!HotelPAX & "') AND ((AllPrices_ww.Sdate)<=#" & sdate & "#) AND ((AllPrices_ww.edate)>=#" & sdate & "#) AND ((AllPrices_ww.HtCode)='" & dbrs!HotelCode & "'));")

Above is where we should be getting data but as the date is interpred wrong then my results are wrong, you can see the first time it is ran sdate should be my globaldate, I then add on the number of days from the db

Do Until hotelrs.EOF



'Lets convert to GBP using the ExRate from the table
exrate = hotelrs!ExValue
' Chech not null
If IsNull(hotelrs![curcosts]) Then
MsgBox "Hotel: " & dbrs!HotelCode & " has no cost associated using 0"
costgbp = 0
Else
costgbp = (hotelrs![curcosts] / exrate) * dbrs!NumNights
End If

DoCmd.SetWarnings (0)
DoCmd.RunSQL "INSERT into tbl_temp_mcgrid (McCode,HotelCode,StartDate,EndDate,CostUKP,NumNights,Occupancy,gstartdate) VALUES ('" & Me.McCode & "','" & dbrs!HotelCode & "',#" & sdate & "#,#" & edate & "#,'" & costgbp & "', '" & dbrs!NumNights & "','" & dbrs!HotelPAX & "', #" & globaldate & "#)"
DoCmd.SetWarnings (1)

When I run the above the dates are inputted in the US format.

total = total + (costgbp * dbrs!NumNights)

hotelrs.MoveNext
Loop
sdate = edate
edate = sdate + dbrs!NumNights

dbrs.MoveNext
Loop

I have tried using the format function but I seem to be getting the same problem.

Thanks in advance



Trancemission
=============
If it's logical, it'll work!

 

[SkipVought]

Hi,

Very Important! Lots of missunderstanding in MS Applications.

REAL DATES are just NUMBERS -- like today's date serial value is 38285. What you SEE is a format DISPLAY value.

To get year, month & day, use date functions, NOT string functions!

MyYear = Year(MyDate)
MyMonth = Year(MyDate)

There's DateDiff or you can do simple arithmatic operations like ADDING a span of days to a date.


Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[Trancemission]

mmmm along with your reply and MS applications i am now totally confused.

I understand how you can use the day() month() functions but fail to understand why I need to use these.

If i have a date and add x days to it, I then do a lookup on the database to find the band which this date falls in. All I have using the day() function is the day number,I know I can get month and year information aswell but how do I use this in a query?

As I am from a Unix programming background I use the unix timestamp all the time for date functions, I wasn't aware that access used this and if it does how do you access this and manipulate accordingly.

Thanks

Trancemission
=============
If it's logical, it'll work!

 

[SkipVought]

I'm not going to wade thru your code.

I did see where you're using the Split STRING function to "switch" the month and day. DOES NOT MATTER! Its just a DISPLAY. globaldate is ALREADY a date. No need to do contortions to "FIX" it. sdate is ALREADY a date! No need to CONVERT it from a string to a date via #.

((AllPrices_ww.Sdate)<=" & sdate & ") AND ((AllPrices_ww.edate)>=" & sdate & ")

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[RickSpr]

Thanks for providing so much detail--though I admit it's kind of overwhelming in this case, and I find it somewhat hard to follow.

Here are some relevant facts. Check your assumptions against these first. If any of these are not what you expected, you'll probably need to revise your code.

1. Date order is not an issue for the values of table fields or Date variables, because the internal value (ignoring the time part) is just a serial number of the day. Day 1 is December 31, 1899, day 2 is January 1, 1900, and today's serial number is 38285.

2. An unambiguous date (e.g. "26 January 2004" or "26/01/2004") will always be interpreted correctly, either in VBA code or as input in a datasheet or form control.

3. The default date order displayed in a form, report, or datasheet depends on the Regional Settings in the Windows control panel, unless you override that using the Format property.

The following facts apply only to ambiguous date formats either entered as literal constants in VBA code, or entered into a datasheet or form control.

4. As you already mentioned, though not quite as specifically, ambiguous date constants in VBA code and SQL statements are interpreted in mdy order. It is not possible to change this.

5. The date order entered into a form or datasheet is interpreted according to the Regional Settings. The Format property, which only applies to output, doesn't affect this.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Trancemission]

I think I understand what you are saying, let me try and show you what I am trying to do

I have a global date 31/12/2004 [dd/mm/yyyy] which at the moment is hardcoded in my code.

I then create a recordset from a table which contains [usually about 5] rows of data.

I loop thorugh this recordset and one of the fields is a integer [NumDays]. As I loop through I need to add this field [NumDays] to my globaldate. So my global date will keep increasing by x days

Using this new date I then lookup in another table for a record where my new date falls in a band of dates [fields are sdate and edate] I then add this data to another table

At the moment I am inputting my date in the format dd/mm/yyyy but in VBA it is converted straight to mm/dd/yyyy. As I now understand this is irrelevent.

Please could you also clarify:

An unambiguous date (e.g. "26 January 2004" or "26/01/2004") will always be interpreted correctly, either in VBA code or as input in a datasheet or form control.

How can this be true? how does access know I mean 3rd of febuary and not 2nd of march when I use 03/02/2004?

Cheers

Trancemission
=============
If it's logical, it'll work!

 

[SkipVought]

I have a global date 31/12/2004 [dd/mm/yyyy] which at the moment is hardcoded in my code

FORGET FORMAT. Use DateSerial to make the date.

globaldate = dateserial(2004,12,31)

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[PHV]

Quick and dirty, replace this:
AND ((AllPrices_ww.Sdate)<=#" & sdate & "#) AND ((AllPrices_ww.edate)>=#" & sdate & "#) AND
By this:
AND AllPrices_ww.Sdate<=#" & Format(sdate, "m/dd/yyyy") & "# AND AllPrices_ww.edate>=#" & Format(sdate, "m/dd/yyyy") & "# AND

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Yup - PHV's above suggestion would disambiguate UK dates. Some of us need to take it another step, using either "yyyy-mm-dd" or "mm\/dd\/yyyy", some more info and tips can be found at International Dates in Access and Return Dates in US #mm/dd/yyyy# format (usage of backslashes is explained in the first link).

SkipVought - sorry, I don't understand. Do you mean firing off the below sql string to the Jet engine (or any db engine):

[tt]"select * from mytable where mydate = " & date[/tt]

should work regardless of locale?

I'd agreed, had you used for instance cdbl(date) or clng(date) (but would that apply for other databases and platforms than Microsoft?), but the above used with my locale gives 3075 Syntax error in query expression 'mydate = 25.10.2004'.

Roy-Vidar

 

[RickSpr]

Please could you also clarify:

Quote:
An unambiguous date (e.g. "26 January 2004" or "26/01/2004") will always be interpreted correctly, either in VBA code or as input in a datasheet or form control.

How can this be true? how does access know I mean 3rd of febuary and not 2nd of march when I use 03/02/2004?

Well, 03/02/2004 is an ambiguous date, for exactly the reason you're questioning it. By "unambiguous", I meant dates that can not be misinterpreted, either because the numbers are out of range (anything over 31 must be a year, anything over 12 must be a day or a year) or because the month name is spelled out. VBA does this kind of piecemeal analysis every time it converts a string to a date.

BTW, that's not necessarily a good thing at all. On a system with Regional Settings specifying date format dd/mm/yy, an entered date of 5/13 will be interpreted as May 13th (of the current year), but 5/12 will be interpreted as December 5th! If a user is inputting dates in a format that doesn't match Regional Settings, some inputs will be rejected as invalid, while others will be accepted but stored as a different date from what the user intended. Or, if different users are entering dates in different date orders (but with the same Regional Settings), some of the dates will be right and some will be wrong, and you'll have a hard time figuring out which is which because, after all, the wrong dates are all ambiguous! I would have preferred it if Microsoft had refused input dates that don't match the Regional Settings format, or at least given us an option to reject them.

At the moment I am inputting my date in the format dd/mm/yyyy but in VBA it is converted straight to mm/dd/yyyy. As I now understand this is irrelevent.

Well, to be technical, VBA converts it to internal format (the serial number form), which has no date order. Is that what you meant when you said it's irrelevant?

All of the date operations you describe, except for the hardcoded date itself, are taking place on internal dates stored in tables or variables. The hardcoded date, because it's in VBA source code, must be coded as #12/31/2004#, but other than that you shouldn't have a problem with the logic you describe. (I'm assuming your Regional Settings date format is dd/mm/yy, since that's how you're inputting dates.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Trancemission]

Thanks Rick, I pretty much understand now - but my contract is finished so back so my little safe unix world

Yet again I seem to have stumbled into the trap of MS assuming that I am ignortant - is there not a way to view the serial code in the datasheet view of a table - although a quick query would do - if only I had known this a week ago.

Anyways thanks for your responses.

Trancemission
=============
If it's logical, it'll work!

 

[RickSpr]

Glad you asked that! At first I was going to say you couldn't do it with a table, but because you made me think about it, I decided to try applying a numeric Format property to see if Access would convert it automatically, and it does! So you can simply change the table (or query or form or report) column's Format property to a predefined or custom numeric format ("Fixed" works well) and it will show you the serial form dates.

Don't be surprised if you see a non-zero fraction there. The fractional part of a stored date represents the time of day, expressed as a fraction of a day. If the field is set to Date(), the fraction will be zero, but if it's set to Now() it will include the time.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein