date formating in a query

[Monzer]

Hi everyone;
I feel so embaress for the question I'm going to ask. Its been a really long time since I worked with access querys.
I have a feild in my customer table called[SaleDate]mm/dd/yy, now I need to display that feild in a query as followed yyyymmdd. Someone told me I'd be better off using a subString in my query. How do I do that.
Any help would be appreciated.
Thank you in advace

 

[SkipVought]

Hi,

In the query
[tt]
Format([SaleDate], "mm/dd/yy")
[/tt]
assuming that [SaleDate] is TRUELY a DATE!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[lespaul]

Actually I think he's got it in mm/dd/yy format and wants to convert to YYYYMMDD

Format([SaleDate], "YYYYMMDD")

Leslie

 

[Monzer]

Thank you very much guys. It worked

 

[CaptainBob007]

Where exactly do I insert this in the query? I'm doing a UNION query and am trying to format the date with mm/dd/yyyy. I've tried putting it after the WHERE clause, between the FROM and WHERE clauses, and before the FROM clause, and no luck. Does it have to go right next to where the data from the source tables are selected or what?

~Bob

 

[SkipVought]

Bob,

What EXACTLY is your Query so we can tell you EXACTLY where to put what? Monxer wanted to DISPLAY a date field in "mm/dd/yy" format.

So it DEPENDS what you want to do with the string.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[CaptainBob007]

Hey Skip, here's my exact query (in SQL obviously - cant use the design view for a union query).

SELECT tblCons.ConID, tblCons.ConLicNum, tblCons.ConName, tblCons.ConLicIssue, tblCons.ConType, tblCons.Price FROM tblCons where ConLicNum is not null
UNION SELECT qryConHistName.ConID, qryConHistName.ConLicNum, qryConHistName.ConName, qryConHistName.ConLicIssue, qryConHistName.ConType, qryConHistName.Price 
FROM qryConHistName
Where ConLicNum Is not null;

So I'm combining values from tblCons and qryConHistName. In both tblCons and qryConHistName, the format for the field ConLicIssue is "short date" (mm/dd/yyyy), yet in this query, some of the ConLicIssue values have the general date (both date and time), which messes things up when I'm trying to do reports for certain date ranges (using the BETWEEN function). I dont know how they become general date since the query is selecting only from short dates, but nevertheless they do.

I'd really like this query to output all ConLicIssue values in the short date format. I'm hoping this can be done. Any advice would be greatly appreciated.

~Bob

 

[PHV]

Have you tried something like this ?
SELECT ConID,ConLicNum,ConName,Format(ConLicIssue,"mm/dd/yyyy") As _ConLicIssue,ConType,Price
FROM tblCons
WHERE ConLicNum IS NOT NULL
UNION
SELECT ConID,ConLicNum,ConName,Format(ConLicIssue,"mm/dd/yyyy") As _ConLicIssue,ConType,Price
FROM qryConHistName
WHERE ConLicNum IS NOT NULL;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

A DATE is a DATE. It is a NUMBER like 37456.12345. What you SEE is a Date Format.

So 2 Dates might be the same Year,Month & Day but could be DIFFERENT if the TIME portion (the part less than ONE) is NOT EQUAL.

SELECT tblCons.ConID, tblCons.ConLicNum, tblCons.ConName, [b]format(tblCons.ConLicIssue, "mm/dd/yyyy")[/b], tblCons.ConType, tblCons.Price FROM tblCons where ConLicNum is not null
UNION SELECT qryConHistName.ConID, qryConHistName.ConLicNum, qryConHistName.ConName, qryConHistName.ConLicIssue, qryConHistName.ConType, qryConHistName.Price 
FROM qryConHistName
Where ConLicNum Is not null;

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[CaptainBob007]

Ok, that works. I have renamed the newly formatted field "IssueDate", so the part of the statement is now:

format(ConLicIssue, "m/d/yyyy") As IssueDate,

Now I'm trying to ORDER BY IssueDate, and it doesn't work at all.

If I'm understanding it correctly, the "format" makes the value no longer a date but a string instead. Since I have to order by date, is this the proper way to go about doing it all?

~Bob

 

[lespaul]

You have to:

ORDER BY format(ConLicIssue, "m/d/yyyy")

you can't use the alias

HTH

Leslie

 

[PHV]

You can use ORDER BY ColumnNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244