More date fun

[bigal.nz]

Hiya

More date issues. I have a input form with text boxes for two dates that should be passed to a query, and then open another form to display the result set.

I was going to acheive this with some VBA. The first hurdle was converting by dates from dd/mm/yyyy to #mm/dd/yyyy#

I have this code which converts my first date (01/03/2016) but not the second date (21/03/2016)



Private Sub CmdSAMInput_Click()

Dim DateStart As Date, DateEnd As Date
Dim strSQL As String

' DoCmd.OpenForm "SAM Input", acNormal, "", "[DateStart] BETWEEN #" & Me.Date_Start & "# AND #" & Me.Date_End & "#", , acNormal

DateStart = Format(Me.Date_Start, "mm\/dd\/yyyy hh\:nn\:ss")
DateEnd = Format(Me.Date_End, "mm\/dd\/yyyy hh\:nn\:ss")
MsgBox ("Dates Formatted : " & DateStart & " " & DateEnd)


End Sub



Any idea why?

Cheers

-Al

[SamT]

Me.Date_Start looks like a UserForm Control. If so, its Value is already a String in the proper format, so
DateStart = "#" & Me.Date_Start & "#"should work

To reformat a String date, first you have to convert it to a Date Type. For example to change Me.Date_Start to "1-3-16"

NewFormatDate = Format(CDate(Me.Date_Start), "m-d-yy")
Your Format String returns "03/20/2016 00:00:00".
The same as "mm/dd/yyyy hh:mm:ss".

I've never seen a Format string like that and it's not in my Number Formats book. Maybe VBA works different UpSide Down :dunno

[bigal.nz]

Me.Date_Start looks like a UserForm Control. If so, its Value is already a String in the proper format, so
DateStart = "#" & Me.Date_Start & "#"should work


But the reason I am trying to convert it from my locale format (dd/mm/yyyy) to mm/dd/yyyy is beacuse I have read that to use dates in VBA in Access you must put it into US format. Is this not true?


To reformat a String date, first you have to convert it to a Date Type. For example to change Me.Date_Start to "1-3-16"

NewFormatDate = Format(CDate(Me.Date_Start), "m-d-yy")
Your Format String returns "03/20/2016 00:00:00".
The same as "mm/dd/yyyy hh:mm:ss".

I've never seen a Format string like that and it's not in my Number Formats book. Maybe VBA works different UpSide Down :dunno

Incidentially my dates are actually dates AND times. Does this make any difference?

Cheers

-Al

[bigal.nz]

I tried :



Private Sub CmdSAMInput_Click()
'Debug.Print "[DateStart] BETWEEN #" & Me.Date_Start & "# AND #" & Me.Date_End & "#"
' DoCmd.OpenForm "SAM Input", acNormal, "", "[DateStart] BETWEEN #" & Me.Date_Start & "# AND #" & Me.Date_End & "#", , acNormal
Dim DateStart As Date, DateEnd As Date
Dim strSQL As String

' DoCmd.OpenForm "SAM Input", acNormal, "", "[DateStart] BETWEEN #" & Me.Date_Start & "# AND #" & Me.Date_End & "#", , acNormal

DateStart = Format(Me.Date_Start, "mm/dd/yyyy hh:mm:ss")
DateEnd = Format(Me.Date_End, "mm/dd/yyyy hh:mm:ss")
MsgBox ("Dates Formatted : " & DateStart & " " & DateEnd)


End Sub


But it doesnt convert the second date:

15713

[bigal.nz]

Actually I know what is going on. The format function thinks 2/03/2016 is fine to convert to 03/02/2016 (note that the 3 could be a valid month or year)

In order to work out what was going on, I tried the date 12/03/2016 (note that 12 could be a valid month) and it changed in format from 12/03/2016 to 03/12/2016.

I then tried 13/03/2016 and it would not convert to 03/13/2016.

So for some reason Access refuses to convert dd/mm/yyyy where dd is over 12.

[SamT]

But the reason I am trying to convert it from my locale format (dd/mm/yyyy) to mm/dd/yyyy is beacuse I have read that to use dates in VBA in Access you must put it into US format. Is this not true? I don't know, I don't use Access. Code is code, so if you hum a few bars of Access, I can follow along. But that explains why the EndDate didn't cross over, you needed to convert it to a Date Type first.


Incidentially my dates are actually dates AND times. Does this make any difference? Depends If the Field in the Access Table is a Date, then Yes, If that field is a mix of Dates and Times, then you should probably use a Greater Than and a Lessor Than, because I doubt that your time exactly matches the one in the Access Table Field.

You will need to determine exactly what Access has, then use the CDate Function inside the Format Function

These are the Field types in Access
Attachment (https://support.office.com/en-us/article/Introduction-to-data-types-and-field-properties-30AD644F-946C-442E-8BD2-BE067361987C#bm2a)

AutoNumber (https://support.office.com/en-us/article/Introduction-to-data-types-and-field-properties-30AD644F-946C-442E-8BD2-BE067361987C#bm2b)
Currency (https://support.office.com/en-us/article/Introduction-to-data-types-and-field-properties-30AD644F-946C-442E-8BD2-BE067361987C#bm2c)
Date/Time (https://support.office.com/en-us/article/Introduction-to-data-types-and-field-properties-30AD644F-946C-442E-8BD2-BE067361987C#bm2d)
Hyperlink (https://support.office.com/en-us/article/Introduction-to-data-types-and-field-properties-30AD644F-946C-442E-8BD2-BE067361987C#bm2e)
Memo (https://support.office.com/en-us/article/Introduction-to-data-types-and-field-properties-30AD644F-946C-442E-8BD2-BE067361987C#bm2f)
Number (https://support.office.com/en-us/article/Introduction-to-data-types-and-field-properties-30AD644F-946C-442E-8BD2-BE067361987C#bm2g)
OLE Object (https://support.office.com/en-us/article/Introduction-to-data-types-and-field-properties-30AD644F-946C-442E-8BD2-BE067361987C#bm2h)
Text (https://support.office.com/en-us/article/Introduction-to-data-types-and-field-properties-30AD644F-946C-442E-8BD2-BE067361987C#bm2i)
Yes/No (https://support.office.com/en-us/article/Introduction-to-data-types-and-field-properties-30AD644F-946C-442E-8BD2-BE067361987C#bm2j)


Microsoft has this to say (https://msdn.microsoft.com/en-us/library/ms714540%28v=vs.85%29.aspx):
Date values must be either delimited according to the ODBC canonical date format or delimited by the datetime delimiter ("#"). Otherwise, Microsoft Access will treat the value as an arithmetic expression and will not raise a warning or error.
For example, the date "March 5, 1996" must be represented as {d '1996-03-05'} or #03/05/1996#; otherwise, if only 03/05/1993 is submitted, Microsoft Access will evaluate this as 3 divided by 5 divided by 1996. This value rounds up to the integer 0, and since the zero day maps to 1899-12-31, this is the date used.
A pipe character (|) cannot be used in a date value, even if enclosed in back quotes.


Try this
Dim DateStart As String
Dim DateEnd As String

DateStart = "#" & Format(CDate(Me.Date_Start), "mm/dd/yyyy") & "#"
DateEndt = "#" & Format(CDate(Me.Date_End), "mm/dd/yyyy") & "#"
If that fails, then add the Time Format String, but that means that you are still guessing as to what is really going on with your Access Tables.I understand that some people actually use a Text Type field to store dates. If that is the case, you need to discover the Date Format they used and use that Format with out the "#". Unless they added that to the Texts they are using for dates.:banghead:

[bigal.nz]

Its all good now.

Thanks for your input:hi:

[SamT]

Could you share the solution with our other Members?

You can Use the Thread Tools to mark this thread "Solved."

[bigal.nz]

Of course!!

I was declaring StartDate and EndDate as dates - when they should have been strings.

Doh!

[jonh]

Dates as strings? ok.

Access SQL dates are just like any VBA dates.

VBA requires US format dates just like Access SQL

const d as date = #3/22/2016#

of course something like 1//2/2016 is ambiguous so you can use something like

select * from mytable where mydatefield = cdate('1-feb-2016')


Values within # are literal whereas regional settings are used for formatted strings.