Need some help with date calculations

[Psycence]

Hi, New here so thanks in advance.

I have a section of code on the OnClick event of a button, what it is supposed to do is take the date from the Form on which the button is placed - Form Name - [Dispatch] - Date Field - [dteDate]

Then add one day to the date above and requery a table for all records on this new date.

The code is as follows

Dim DateTomorrow As Date
Dim Day1 As Date

Day1 = [Forms]![Dispatch]![dteDate]

[DateTomorrow] = DateAdd("d", 1, [Day1])

Forms.Dispatch.RecordSource = "Select tbl_Record.* FROM" & _
" tbl_Record WHERE ((tbl_Record.DateR) = #" & [DateTomorrow] & "#)" & _
"Order By tbl_Record.TimeSlot"



Now the problem is that this works perfectly, until I hit the end of the month. It stops adding up the date correctly and returns results for 05/01/2010, When it should be 01/05/2010, then 02/05/2010 and so on.

Any ideas?

Thanks!

 

[DCrake]

This is to do with Americanised dates DD/MM being interpreted as MM/DD

Try DateTomorrow = format(DateAdd("d",1,Date()+1),"Medium Date")

 

[Psycence]

Hi Thanks for the quick reply, I tryied that and no good! I also attempted to format the Day1 field as well to ensure thats pulling through in the correct format and it still did not make any difference.

Below is the code updated as per your suggestion + doing day1 value as well


Dim DateTomorrow As Date
Dim Day1 As Date


Day1 = Format([Forms]![Dispatch]![dteDate], "Medium Date")

[DateTomorrow] = Format(DateAdd("d", 1, [Day1]), "Medium Date")

Forms.Dispatch.RecordSource = "Select tbl_Record.* FROM" & _
" tbl_Record WHERE ((tbl_Record.DateR) = #" & [DateTomorrow] & "#)" & _
"Order By tbl_Record.TimeSlot"

DoCmd.Save acForm, "Dispatch"

 

[DCrake]

You do not need the [] around Day1 in your DateAdd command

Try
? DateAdd("d",1,#30-Apr-2010#)

In the immediate window

 

[Psycence]

Hi DCrake, I tried that and it did not work however I changed my code a little and this seems to work...

Me.dteDate = DateAdd("d", 1, Me.dteDate)

Forms.Dispatch.RecordSource = "Select tbl_Record.* FROM tbl_Record WHERE (((tbl_Record.DateR) = [forms]![Dispatch]![dteDate])) Order By tbl_Record.TimeSlot"

DoCmd.Save acForm, "Dispatch"


The only issue I still have is that the first click seems to default back to the original date, but subsequent clicks move it along nicely with no errors.


Thanks for your help anyway!

 

[DCrake]

Forms.Dispatch.RecordSource = "Select tbl_Record.* FROM tbl_Record WHERE (((tbl_Record.DateR) = [forms]![Dispatch]![dteDate])) Order By tbl_Record.TimeSlot"

Should read

Forms.Dispatch.RecordSource = "Select * FROM tbl_Record WHERE DateR = #" & [forms]![Dispatch]![dteDate] & "# Order By TimeSlot"

Dates need to wrapped in #'s

 

[Psycence]

Ok Im back, the code change I tried infact caused other issues so thats scrapped. Im back to doing the below as I tried the

Try
? DateAdd("d",1,#30-Apr-2010#)

Example but I enter the date and it auto defaults it to American, even if I specify the date as medium date or short date!

Current code -

Private Sub Command20_Click()
Dim DateTomorrow As String
Dim Day1 As String


Day1 = Format([Forms]![Dispatch]![dteDate], "Short Date")

DateTomorrow = Format(DateAdd("d", 1, Day1), "Short Date")

Forms.Dispatch.RecordSource = "Select tbl_Record.* FROM" & _
" tbl_Record WHERE ((tbl_Record.DateR) = #" & Format(DateTomorrow, "Short Date") & "#)" & _
"Order By tbl_Record.TimeSlot"

DoCmd.Save acForm, "Dispatch"

End Sub

 

[Brianwarnock]

When creating Sql strings you have to use US formatted dates, you have the same problem as this guy read the thread and you should come up with your solution.

Brian

 

[Psycence]

Ahh that worked a charm, thanks a lot to all the contributors! Been bugging me for a while that has....

Final Code incase anyone needs wants an example -

Add module as per link in BrianWarnocks post

Then I modified my existing code from my previous post to read -

Dim DateTomorrow As String
Dim Day1 As Date
Dim Day As String

Day = [Forms]![Dispatch]![dteDate]
Day1 = Format(Day, "dd/mm/yyyy")
DateTomorrow = Format(DateAdd("d", 1, Day1), "Short Date")

Forms.Dispatch.RecordSource = "Select tbl_Record.* FROM" & _
" tbl_Record WHERE ((tbl_Record.DateR) = " & SQLDate(DateTomorrow) & ")" & _
"Order By tbl_Record.TimeSlot"

Forms.Dispatch.Requery
DoCmd.Save acForm, "Dispatch"

 

[Brianwarnock]

I may have pointed Psycence at the thread but the code belongs to Allen Browne or is that Allene Brown, I can never remember.

Brian

 

[Psycence]

Ahh yes you are quite correct, A thank you to Mr. Brown/e as well.