Formatting date to have leading zero for DAY

[Praveenevg]

Hello,



I am using following statement current_date = Format(current_date, "m/dd/yyyy")

My goal is to format any dates like 9/1/2014 into 9/01/2014. But this is not working.

Is there a way to format date 9/1/2014 into 9/01/2014? In this case, current_date is a variable of type DATE.

[ItsMe]

I am pretty sure dd is what you want.

What exactly is current_date ??

If you place the formatted text into a control that is formatted for date, the copntrol's format will trump the VBA format.

Perhaps you need a textbox control without formatting.

[John_G]

Hi -

current_date = Format(current_date, "m/dd/yyyy")

You can't use the format that way. The Format function returns a string, not a date. Variables of type Date store a date value in MS Access' internal format. The statement you show above just stores the value of current_date back into itself without changing the format.

In general, Format is used to display a date in whatever format you require on a form or report, but does not affect the internal storage of a date value.

All you need to do is use Format(current_date, "m/dd/yyyy") wherever you want to display the date.

John

[Praveenevg]

I have a table that has dates in m/dd/yyyy format.

In my VBA code, i am trying to use FindFirst on this table for a date.

Table has date value as 9/01/2014. Whereas the variable i am using to search the table has date = 9/1/2014. Since they don't match, FINDFIRST fails.

Therefore, i am trying to convert/format my variable from 9/1/2014 to 9/01/2014.

How can i do this?

[ItsMe]

...Since they don't match, FINDFIRST fails...

They may not match but, they do not match for a reason other than what you suspect.

Maybe you can store you variable as type Date and have better success. At least if it fails with a Date variable you can catch it at initialization of the Date variable and avoid debugging your DAO

Dim MyDate As Date
MyDate = 1 - 1 - 1990

.FindFirst "current_date = " & MyDate

[Praveenevg]

Thanks for the response. But in this case, all my variables are already of type DATE.

I have a table that has dates in m/dd/yyyy format.

In my VBA code, i am trying to use FindFirst on this table for a date.

Table has date value as 9/01/2014. Whereas the variable i am using to search the table has date = 9/1/2014. Since they don't match, FINDFIRST fails.

Therefore, i am trying to convert/format my variable from 9/1/2014 to 9/01/2014.

How can i do this?

[burrina]

Are you using a Combo Box for this? If so, problem solved. It can ONLY find the date with the format in your table.

[Praveenevg]

No.. Not using a combo box.

[ItsMe]

...Whereas the variable i am using to search the table has date = 9/1/2014...

I find this to be an interesting statement. Can you post your VBA here?

[John_G]

In your table - is the field you want to search type Date/Time or type Character? If it is type Date/Time, then it does not have an internal format - what you see is only the display format. The searching method is different for each type.

Was this question not answered for you in this thread https://www.accessforums.net/showthr...3&goto=newpost ?

John

[Praveenevg]

Hello John,

Thanks for response.

I am already using the solution that was provided to me earlier: Here is the statement that is currently failing due to the issue of (Source table having 9/01/2014 and search variable having 9/1/2014):

GNRC.FindFirst "Date = #" & Format(current_date, "m/dd/yyyy") & "#"


Any thoughts?

Here is my code:
DIM current_date AS DATE

current_date holds value 9/1/2014

current_date = Format(current_date, "m/dd/yyyy")

After the format statement, i am expecting 9/01/2014 in variable Current_date; But this is not happening.

[John_G]

After the format statement, i am expecting 9/01/2014 in variable Current_date; But this is not happening.

That's right - it isn't happening. That is because current_date is of type Date; your statement

current_date = Format(current_date, "m/dd/yyyy") does NOT reformat it. A variable of type Date is a special type of numeric data - it stores the date (and time) as a number (it is 41871 today), and you use the Format function to transform that numeric value into a date string.

If you just look at the value in current_date without formatting, it will be formatted in whatever your system date setting is using.

Please tell us the data type of the Date field in your table - is it Date/Time or Text?

If it is type Date, then the FindFirst should work.

John

[June7]

About Access data types http://allenbrowne.com/xbase-05.html

As already noted, a date/time field stores value as a number. Access internal default formatting displays as m/d/yyyy.

If you want to search a date/time field then the parameter must be a date/time value. Is current_date a VBA variable declared as a date type? If it is, don't use the Format function.

So questions you need to answer:

1. is [Date] a date/time field (Date is a reserved word and should not use reserved words as field names)

2. is current_date a variable declared as date type

[Praveenevg]

Hello,

This problem has been resolved after converting the field in the table from a string to a date field. Appreciate help on this so far.

I have a much simpler problem to resolve now, which is related to records failing to get imported into Access table from file, due to this field being a DATE field. Thanks. I should be able to resolve it.