formula works on form but not on report or query?

[mizsydney]

could someone please explain why this formula works perfectly on my form, but not on queries or reports?

=IIf(DateValue([rep_date])>DateValue([org_date]),DateValue([rep_date]),DateValue([org_date]))

thank you in advance for ANY assistance you can offer!

 

[dhookom]

Are any of your date fields Null?
Can you explain what you mean by doesn't work? We can't see your data, your form, your query, your report,...

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]

 

[Crowley16]

the fields in your [], are they names of control boxes on your form?

if so, then you need to use the syntax forms![formName]![controlName] in your queries...

--------------------
Procrastinate Now!

 

[mizsydney]

the fields in the [] are fields from the table, the data is entered into the table from the form.

the error I get on the query and report is that the formula gets the first (correct) date from the first record, and then repeats the same date for all the other records instead of checking each record for the correct date.

using that syntax on the query also gives me the same date for all records.

if I use the forms![formName]![controlName] syntax in the report, it asks for the [rep_date] parameter. if I use the table![tablename][fieldname] syntax, I get a message saying the formula is too complex to evaluate.

what am I doing wrong? sorry to be so clueless!

 

[dhookom]

What is the SQL view of your query? If the query isn't correct, the report won't be correct.

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]

 

[dRahme]

Looks pretty straight forward:

SELECT IIf(DateValue([rep_date])>DateValue([org_date]),DateValue([rep_date]),DateValue([org_date])) AS EvalDate
FROM Table1;

 

[mizsydney]

Access is not exactly learn-as-you-go friendly, is it? thank you so much for your patience!

here is the sql:

SELECT [qry state reporting].ssn1, [qry state reporting].Name, [qry state reporting].APN_New, [qry state reporting].Ind, [qry state reporting].[Date of Claim], [qry state reporting].ssn2, [qry state reporting].[Second name], [qry state reporting].[Primary Name2], [qry state reporting].apprdate, [qry state reporting].Approved, [qry state reporting].name2, IIf(DateValue([rep date])<DateValue([org date]),[rep date],[org date]) AS clm
FROM [qry state reporting], P60_Log;

all I want is to compare [rep date] to [org date] and select the latest date as the date of claim. [date of claim] is currently a field where the user enters the latest date, but that seems redundant so I'd like to have Access do it, and I thought it would be easy. it works fine on the form, but I don't know quite how to get it to the query.

it would really help if I knew what I was doing! thanks again for your patience.

 

[dhookom]

If the two fields are date data type and they have values then there is no reason to use DateValue(). DateValue() might work on a form where the [Rep Date] is also the name of the text box on your form. Try

SELECT [qry state reporting].ssn1, [qry state reporting].Name, [qry state reporting].APN_New, [qry state reporting].Ind, [qry state reporting].[Date of Claim], [qry state reporting].ssn2, [qry state reporting].[Second name], [qry state reporting].[Primary Name2], [qry state reporting].apprdate, [qry state reporting].Approved, [qry state reporting].name2, IIf([rep date]<[org date],[rep date],[org date]) AS clm
FROM [qry state reporting], P60_Log;

Also, why is there no join between [qry state reporting] and P60_Log? Does one or the other of these return only a single record? Which table contains [Rep Date] and [Org Date]?

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]

 

[mizsydney]

dhookum - unfortunately I am so Access-clueless that I don't even understand your question. I will do my best tho!

when I first tried comparing the dates, just using the field names wouldn't work. I borrowed the DateValue from another Tek-Tips post and it started working, at least on the form. [rep date] is the name of a text box on the form and also the P60_log table. (when you enter the date on the form it is stored in the table.)

I don't know why there is no join between [qry state reporting] and P60_Log. probably because I don't know what I'm doing?

there is basically only the one table, the p60_log. (I have a couple other small tables, but I haven't quite figured out how to use them so the info is typed in manually at this point.)

neither [qry state reporting] and P60_Log should return a single record - P60 log is the one table, and the qry state reporting asks for a start and end date for the report of claims approved during that period.

[Rep Date] and [Org Date] are both in my one P60_log table. the dates are entered from frm claim record.

I hope this info makes sense.

I appreciate your patience and your assistance! it must be really frustrating dealing with an Access Dummy like myself!

 

[mizsydney]

oops! I forgot - I tried your SQL, but I get the same error - the same date for all the records.

 

[dhookom]

You should start over and tell us about the table and query as well as your data. If they both return more than one record there should be some kind of join or criteria in the query. If you don't join, you will see lots of repeating field values.

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]

 

[mizsydney]

for crying out loud.

everything works great today! I don't know what changed, but whatever it was I am pleased!

thank you so much for all your assistance!!! and your patience!