Date format in VBA

[imfarhan]

Dim dtMdate As Date
dtMdate = Format(rs.Fields("Mdate"), "yyyymmdd")


Debug.print dtMdate



But when I print the above variable in my Intermeditate window I got the
values like
debug.Print dtmdate
00:00:00 instead of date value like dd/mm/yyyy

Am I using wrong way to use format mask?
Many thanks
Farhan

 

[boblarson]

Are you sure that there is a value in the record that you are on there? That looks like it is returning nothing so it gives you that answer.

 

[james_lankford]

what data type is rs.Fields("Mdate") ?
a date ? a text ?

Dim dtMdate As Date

dtMdate = rs.Fields("Mdate")
 
Debug.print dtMdate

shouldn't this work ?
why do you need the format at all ?

 

[Norie]

Farhan

The format function returns a string not a date.

Some times VBA will recognise the string as a date and convert it, some times it won't.

You could try using something like DateValue or DateSerial.

Or as James has suggested - don't do anything, which might work if the field is date or text (see above about converting strings to date).

 

[boblarson]

Farhan

The format function returns a string not a date.

Some times VBA will recognise the string as a date and convert it, some times it won't.

And in this case using yyyymmdd it does not recognize that as a date value. So, you are right about that.

If they want to use that format then they need to declare their variable as a string. If they want it as a date then they would have to use a valid date format. I didn't even think about that earlier. Good catch.

 

[imfarhan]

Hi All,
Thanks for your swift response.

When I don't use the format its work fine but it bring the time values too which I don't want to display becuase further in my VBA code I want to use the DateDiff function which mentioned below
I want to calcuate the variable date value with current date only.

Set rs= CurrentDb.OpenRecordset("select * from maxdate)

Note the maxdate is the query not table
and orginal field is date/time type mdate is the

The MaxDate query is like:-
Select Max(CeStartDateTime) from <table>

Further I would like to use ...thats why I dont want time stamp
IF DateDiff("d",date,mDate) <2 Then
X
Else
Y
End IF

Many thanks
Farhan

 

[james_lankford]

I think this might work to get rid of the time from the datetime and I think its also somewhat compatible across different systems
I didn't really look in detail at your last post.

SELECT 
    DATEADD( 'd', 
             DATEDIFF( 'd', 0, Mdate), 
             0 
           ) as my_date
FROM 
  example_table;