Problem with dates

[Lewe22]

On button click, what i'm trying to do is (in steps)

• Find Current Date [Date]

• Return First Date of Current Month [New_Date] (Eg: 10/06/2008 would become 01/06/2008)

• Use [New_Date] in a query to pull out [tblPayMonths].[MONTH_ID]

• Reflect [MONTH_ID] in a field on a form called txtMonthID.

[tblPayMonths] contains the following sample data:

MONTH_ID    SESS    MONTH    MONTH_START_DATE    MONTH_END_DATE
07-JUN08    07      JUN      01/06/2008          30/06/2008
07-JUL08    07      JUL      01/07/2008          31/07/2008
08-AUG08    08      AUG      01/08/2008          31/08/2008
08-SEP08    08      SEP      01/09/2008          30/09/2008

I have the following code:

Private Sub cmdTest_Click()
On Error GoTo Err_cmdTest_Click

    Dim dbsCTrack As DAO.Database
    Dim rstGenerateMonthID As DAO.Recordset
    Dim sqlGenerateMonthID As String
    
    Dim NewDate As Date
    NewDate = DateSerial(Year(Date), Month(Date), 1)
    Me.txtMonthID = NewDate
    
    Set dbsCTrack = CurrentDb
    sqlGenerateMonthID = "SELECT month_id FROM tblPayMonths WHERE (((month_start_date)=#" & NewDate & "#))"
    Set rstGenerateMonthID = dbsCTrack.OpenRecordset(sqlGenerateMonthID)
    
    MsgBox sqlGenerateMonthID
    
    Me.txtMonthID = rstGenerateMonthID(1)
    rstGenerateMonthID.Close

Exit_cmdTest_Click:
    Exit Sub

Err_cmdTest_Click:
    MsgBox Err.Description
    Resume Exit_cmdTest_Click

End Sub

As you can see all dates are held in the table in the format dd/mm/yyyy.

However, when i try to query my table using the above SQL statement i am receiving the error message: "Item not found in this collection"

I have noticed when created as an access query with the parameter '01/06/2008' the SQL automatically produced swaps the date into US format like this.....

SELECT tblPayMonths.MONTH_ID FROM tblPayMonths WHERE (((tblPayMonths.MONTH_START_DATE)=#6/1/2008#));

.....so i believe this is where it is failing.

I'm really confused here about the formats, and why it would change back to US when all dates are stored as UK short dates. Can anyone suggest an alternative as i'm well and truely perplexed.

[Lewe22]

I've just noticed that line 18 should have '0' in place of '1' and i have now changed it in my code.

Me.txtMonthID = rstGenerateMonthID(0)

Still none the wiser though. Please HELP!

[patjones]

Hi Lewe:


There are a couple of comments I have. I don't know that they will necessarily translate to solutions. First, what I noticed is that when I ran

 DateSerial(Year(Date), Month(Date), 1)

in my Immediate Window, I got "6/1/2008" back. In other words, it gave me mm/dd/yyyy format, as you discovered when using the Query Builder to get Access' version of the SQL statement. Sometimes what I end up doing with dates is defining them as strings so that Access/VB does not impose any kind of automatic formatting on them.

Another thing you can try is to coax the date into the format you want by doing this:

 
NewDate=Format(DateSerial(Year(Date), Month(Date), 1), "dd/mm/yyyy")

When I ran that in my Immediate Window, I got the date in the format that you are looking for.

Finally, I have a feeling that the error message you are getting might be referring to the way that you are trying to referece the recordset. Usually, when you try to make an assignment like what you are doing on line 18, you not only have to specify the record number, but also what field in the record you want, such as:

 Me.txtMonthID = rstGenerateMonthID(1)!month_id

or

 Me.txtMonthID = rstGenerateMonthID(1).month_id

If these ideas don't pan out, set a breakpoint in the code and step through it to determine exactly what line it's failing on.

Pat

[Stewart Ross]

Hi. In addition to Zepphead's comments, bear in mind that a date is not stored in a particular dd/mm/yy format at all; it is stored as a date/time value which has an integer component, representing the number of days since a reference date of 1 Jan 1900, and a decimal part which represents the time. The format controls how it is displayed, not how it is stored.

You are running into difficulties because the ANSI SQL standard uses the American format for date literals, regardless of the regional settings of your PC - hence mm/dd/yyyy in immediate window. You can always use Format to show the date in any other format you like - Format(somedate , "dd/mm/yyyy") will return the normal long-year UK form of the date somedate, for instance.

See also the following HowTo article by NeoPa on Literal Date Times and their Delimiters

-Stewart

[missinglinq]

Allen Browne has a short paper on international date formats and working with them that might be of use to you:

http://allenbrowne.com/ser-36.html

Linq ;0)>

[Lewe22]

Thanks guys. What i did to fix this was set the format to US date at the beginning.

NewDate = Format(DateSerial(Year(Date), Month(Date), 1), "mm/dd/yyyy")

[youmike]

America has left the world two legacies which get to me. One is Letter paper, as opposed to A4. The other mm/dd/yy dates. Personally, I format all dates to yyyy/mm/dd but use Cdate and # separators or other such devices whenever there may be inconsistency. One reason for using yyyy/mm/dd is that they can be sorted consistently as is.

One could write pages and pages on the topic, but the references given earlier are very relevant and immediately available