How Convert Date to Text

[Walter]

I need to convert the date that comes up in the result of an access query to
text for exporting to Excel. I want the 5 digit number as the result rather
than a normal date. So instead of 9/26/2008, I want the result to be 39717.
Can I do a function or expression in a query to provide that result?

 

[KARL DEWEY]

Int([YourDateField])

 

[Walter]

1stAuditDate: Int([FirstAuditDate])

I placed this in the field row of my query but keep getting an error. I
tried to put it in the criteria row but get an ODBC error asking me to use
the CONVERT function which does not seem to work either. Am I entering it in
the wrong location?

Int([YourDateField])

--
KARL DEWEY
Build a little - Test a little

I need to convert the date that comes up in the result of an access query to
text for exporting to Excel. I want the 5 digit number as the result rather
than a normal date. So instead of 9/26/2008, I want the result to be 39717.
Can I do a function or expression in a query to provide that result?

 

[fredg]

I need to convert the date that comes up in the result of an access query to
text for exporting to Excel. I want the 5 digit number as the result rather
than a normal date. So instead of 9/26/2008, I want the result to be 39717.
Can I do a function or expression in a query to provide that result?

Covert to Text. Why text? I'm sure you meant Number.
But then, you can export the date field as a regular date, so why
change it to it's number (or text) value?

As a Number it must be a Long Integer value:
Clng([YourDateField])

? CLng(Date())
39717

to accommodate any date value larger than the maximum Integer size of
32,767 (which is 9/16/1989).

If you must have the result as a String, then:

? CStr(CLng(Date()))
39717

 

[KARL DEWEY]

I placed this in the field row of my query but keep getting an error.
What error did you get?
--
KARL DEWEY
Build a little - Test a little

1stAuditDate: Int([FirstAuditDate])

I placed this in the field row of my query but keep getting an error. I
tried to put it in the criteria row but get an ODBC error asking me to use
the CONVERT function which does not seem to work either. Am I entering it in
the wrong location?

Int([YourDateField])

--
KARL DEWEY
Build a little - Test a little

I need to convert the date that comes up in the result of an access query to
text for exporting to Excel. I want the 5 digit number as the result rather
than a normal date. So instead of 9/26/2008, I want the result to be 39717.
Can I do a function or expression in a query to provide that result?

 

[fredg]

1stAuditDate: Int([FirstAuditDate])

I placed this in the field row of my query but keep getting an error. I
tried to put it in the criteria row but get an ODBC error asking me to use
the CONVERT function which does not seem to work either. Am I entering it in
the wrong location?

Int([YourDateField])

--
KARL DEWEY
Build a little - Test a little

I need to convert the date that comes up in the result of an access query to
text for exporting to Excel. I want the 5 digit number as the result rather
than a normal date. So instead of 9/26/2008, I want the result to be 39717.
Can I do a function or expression in a query to provide that result?

See my reply....

 

[Walter]

What I am actually doing is a MS Query to pull over data to Excel and then I
need to manipulate the data to come up in 4 buckets: Over 11 weeks, 6 to 10
weeks, 1 to 5 weeks, and Current Week. I am having a hard time writing a
nested If statement for that to look at the date and then in another column
fill the cell with one of the buckets noted above. Right now my statement
looks like this:

If(D2 <= today()-77,"11+ Weeks",If(D2 < today()-70,"6 to 10 Weeks",If(D2 <
today()-35,"1 to 5 Weeks",If(D2 > today()-7,"Current Week"))))

It seems to run okay for 11+ Weeks and Current Week but not the other two
buckets. Perhaps it would be better to do in VBA and attach the code to a
button but not sure how to write that since I am new to programming.

Thanks for your time!

I need to convert the date that comes up in the result of an access query to
text for exporting to Excel. I want the 5 digit number as the result rather
than a normal date. So instead of 9/26/2008, I want the result to be 39717.
Can I do a function or expression in a query to provide that result?

Covert to Text. Why text? I'm sure you meant Number.
But then, you can export the date field as a regular date, so why
change it to it's number (or text) value?

As a Number it must be a Long Integer value:
Clng([YourDateField])

? CLng(Date())
39717

to accommodate any date value larger than the maximum Integer size of
32,767 (which is 9/16/1989).

If you must have the result as a String, then:

? CStr(CLng(Date()))
39717

 

[Dale Fye]

I think your comparisons are wrong. You need to be comparing to the
following, in this sequence.

<= Today() - 77 "11+ weeks"
<= Today() - 42 "6 to 10 weeks"
<= Today() - 7 "1 to 5 weeks"

Today() - 7 "Current week"

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

What I am actually doing is a MS Query to pull over data to Excel and then I
need to manipulate the data to come up in 4 buckets: Over 11 weeks, 6 to 10
weeks, 1 to 5 weeks, and Current Week. I am having a hard time writing a
nested If statement for that to look at the date and then in another column
fill the cell with one of the buckets noted above. Right now my statement
looks like this:

If(D2 <= today()-77,"11+ Weeks",If(D2 < today()-70,"6 to 10 Weeks",If(D2 <
today()-35,"1 to 5 Weeks",If(D2 > today()-7,"Current Week"))))

It seems to run okay for 11+ Weeks and Current Week but not the other two
buckets. Perhaps it would be better to do in VBA and attach the code to a
button but not sure how to write that since I am new to programming.

Thanks for your time!

I need to convert the date that comes up in the result of an access query to
text for exporting to Excel. I want the 5 digit number as the result rather
than a normal date. So instead of 9/26/2008, I want the result to be 39717.
Can I do a function or expression in a query to provide that result?

Covert to Text. Why text? I'm sure you meant Number.
But then, you can export the date field as a regular date, so why
change it to it's number (or text) value?

As a Number it must be a Long Integer value:
Clng([YourDateField])

? CLng(Date())
39717

to accommodate any date value larger than the maximum Integer size of
32,767 (which is 9/16/1989).

If you must have the result as a String, then:

? CStr(CLng(Date()))
39717