Date format problem

[andy_25]

Hi,

I am using someone elses code and I am struggling to get it to so what I want, could you help please.

Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([Job_Date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If

We use dd/mm/yyyy but I cannot figure out how and where I tell it this. In the Code it states to "Use the format string to add the # delimiters and get the right international format." But where do I put the # delimiters?

Thanks in advance,

Andy

 

[andy_25]

Appologies for posting in wrong forum...

 

[Kiwiman]

Howzit

You can try

Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([Job_Date] >= [b]#[/b]" & Format(Me.txtStartDate, conJetDate) & "[b]#[/b]) AND "
End If

 

[Brianwarnock]

I think this is Allen Browne code, any way what he is saying is this
Format(Me.txtStartDate, conJetDate)

will format the date properly as it uses this
Const conJetDate = "\#mm\/dd\/yyyy\#"

Brian

 

[andy_25]

Kiwiman

Thanks for the reply, I believe I have tried that, I get a Syntax error. I'm sure that you are along the right lines but it just will not work for some reason.

Andy

 

[andy_25]

I think this is Allen Browne code, any way what he is saying is this
Format(Me.txtStartDate, conJetDate)

will format the date properly as it uses this
Const conJetDate = "\#mm\/dd\/yyyy\#"

Brian

Hi Brian,

It is indeed Allen Browne code: http://allenbrowne.com/ser-62.html

But that format isn't proper for the UK and changing the Const conJetDate = "\#dd\/mm\/yyyy\#"does not put it in the correct format.

Any ideas how to get it to use dd/mm/yyyy?

 

[Brianwarnock]

Its a long time since I had any dealings with the code but as I remember it, it is designed so that the date, no matter how inputted , is formatted to the correct format, ie American, for use in the SQL string.
You can not use any other short date format in SQL or VBA, but this is not a problem for your input or reports etc.

Brian

 

[andy_25]

Its a long time since I had any dealings with the code but as I remember it, it is designed so that the date, no matter how inputted , is formatted to the correct format, ie American, for use in the SQL string.
You can not use any other short date format in SQL or VBA, but this is not a problem for your input or reports etc.

Brian

So you are saying that I must get the user to enter the date american style. Surely not.

I will figure out a work around then... Date picker formated to enter mm/dd/yyyy maybe.

Thanks for the help,

Andy

 

[Brianwarnock]

No I did not say that, you enter the date any style and the code converts it.

it is designed so that the date, no matter how inputted , is formatted to the correct format, ie American,

Brian

 

[Brianwarnock]

Perhaps I should point out that dates are all stored the same way as a Double with the numbers before the decimal point giving the date and the decimal giving the time, so you can mess with reformatting without affecting the underlying data.

Brian

 

[gemma-the-husky]

instead of all that, try just

format (txtStartDate,"long date") - as in blue below

i find this generally reliable for distiguishing between regional settings


it might also be that there is no space before the bracket - see red bit below

strWhere = strWhere & "([Job_Date] >= #" & Format(Me.txtStartDate, "long date") & "#) AND "

 

[Kiwiman]

Howzit

I have never tried Allen Brownes method, but I always use (as I definitely struggle with dates) and this gives me consistant results:

strWhere = strWhere & "([Job_Date] >= #" & Format(Me.txtStartDate, [COLOR="Red"]"mm/dd/yy"[/COLOR]) & "#) AND "

 

[andy_25]

Hi all,

Thanks for your input it is much appreciated. It seems that the orginal code is correct (what you would expect from an Access MVP). I just did not understand fully what it does. As Brian said it just formats the date input to what JET expects it to be.

It was actually working in the first place I just got some of the fields confused.

Appologies, and thanks again.

Andy