Date format in a query

[goslincm]

Good morning. I have to fields I need to join in a query, but one is in the date format of xx/xx/xxxx [FYE] and the other just as year xxxx [FiscalYear].

How can I change the FYE field to the year format only for the purpose of running a query?

 

[lespaul]

are they REALLY date fields in the table structure?

Or are they text fields that contain a string that LOOKS like a date?

(check in the design view of the TABLE)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[goslincm]

Leslie, your right, they are both set up as text field types.

 

[lespaul]

Then you can do something like:

INNER JOIN Table1 ON Right(Table1.DateField, 4) = Table2.YearField

Leslie

 

[goslincm]

SELECT [AUDIT REVIEWS].[FYE 2000], dbo_localUnitAudit.AuditReceivedDate, [AUDIT REVIEWS].[DATE COMPLETED], [AUDIT REVIEWS].CountyCd, [AUDIT REVIEWS].LocalUnitType, [AUDIT REVIEWS].LocalUnitCd

FROM [AUDIT REVIEWS],[ dbo_localUnitAudit]
INNER JOIN audit reviews ON RIGHT (Audit reviews.[fye 2000],4) = dbo_localunitaudit.fiscalyear;


I tried to read into your lead out here and plug in my table names and date fields, however this query will not run and I'm not seeing where I'm going wrong.

 

[dhookom]

There shouldn't be a space following "RIGHT" which I would expect to see as "Right(....)". Also, if you allow spaces in your table and field names, you will always need to remember to enclose the individual names in []s.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

SELECT ...
FROM [AUDIT REVIEWS], dbo_localUnitAudit
WHERE Right([Audit reviews].[fye 2000],4) = dbo_localunitaudit.fiscalyear

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[goslincm]

I didn't name the tables in this case, but thought I had [] around them everywhere.
I did delete the space after the word "RIGHT" but it still does not run for me.

Missing Operator error occurs

 

[PHV]

Have you tried my filtered cross join version ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[goslincm]

PHV, I'm getting the two suggestions mixed up in terms of what I am suppose to do. Do I do a normal join and then do the filtering your suggesting? I did the following and of course received an error:

SELECT [AUDIT REVIEWS].[FYE 2000], [dbo_localUnitAudit].AuditReceivedDate, [AUDIT REVIEWS].[DATE COMPLETED], [AUDIT REVIEWS].CountyCd, [AUDIT REVIEWS].LocalUnitType, [AUDIT REVIEWS].LocalUnitCd

FROM [AUDIT REVIEWS]
INNER JOIN [audit reviews].[countycd] = [dbo_localunitaudit], [audit reviews].localunittype=[dbo_localunitaudit].localunittype, [audit reviews].localunitcd = [dbo_localunitaudit].localunitcd
WHERE Right([Audit reviews].[fye 2000],4) = dbo_localunitaudit.fiscalyear;

 

[goslincm]

PHV, disregard previous post. I just found and fixed my problem and it appears to provide what I was after. Thank you both for your help.

CG