Calculated Field with Criteria?

[Guest]

I have a query that has a field created on-the-fly (it picks the first 8 characters of a field) and works fine when I run the query. However, if I add a criteria such as between [startdate] and [enddate] the query says it is too complex. I ran a watch and the between [Forms]![frmName]![txtStartdate] and [Forms]![frmName]![txtEndDate] have proper dates. How can I make this work? I've been pulling out my hair on this trying to solve it!

 

[Guest]

-----Original Message-----
I have a query that has a field created on-the-fly (it

picks the first 8 characters of a field) and works fine
when I run the query. However, if I add a criteria such
as between [startdate] and [enddate] the query says it is
too complex. I ran a watch and the between [Forms]!
[frmName]![txtStartdate] and [Forms]![frmName]!
[txtEndDate] have proper dates. How can I make this
work? I've been pulling out my hair on this trying to
solve it!

.
I had this problem long time ago. I think the first thing

to do is to check that the regional settings of your PC
are the same as the date format of the field you are using
in Access. If the two are different, it always cuases
problems! leon

 

[Allen Browne]

Katie, you probably won't get that structure to work. Consider creating true
date fields alongside of the text fields, so you can easily and efficiently
run the date comparisons and get the results you want.

If you are determined to do it any way, you will need to parse the month,
day and year separately from the beginning of your field using Mid and Left,
then create a date using DateSerial() on the 3 components, then run the
comparion, and then output the result string again and convert it to a date
again before Access will reliably be able to recognise the result. It is
really not worth this effort when it would be much easier to do it
correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I checked that and no difference. In my calculating field I pick from two

different columns to get the 'highest' date. My code is:

LatestDate: Left$(IIf([Next Evaluation]>[Next Report Due],[Next Report Due],[Next Evaluation]),8)

Where Next Evaluation and Next Report Due are text fields with 255 in

length (that's the reason why for the Left$ function.

For the criteria I want the "LatestDate" to be: between [StartDate] and

[EndDate]. I even tried just a simple [enter date] for the criteria and get
the same results.

 

[Guest]

Thanks Allen. I know how to do the DateSerial stuff but get a little lost on trying the rest of the steps within the query. If you could expand a little bit maybe it will make more sense to me. Thanks for your help!

 

[Wayne Morgan]

What do these field contain that you are only wanting the Left 8 characters.
Also, the > will be performed on the entire field, not just the Left 8
characters since the inner parenthesis will be evaluated first. If the
fields are dates, then why not set their data type to date/time instead of
text? Also, the Left$ function returns a string, not a date.

For the Between property to work properly, Access is going to need to know
these are dates.

1) If possible, change the data type of the fields to Date/Time. This is
done in the table.

2) Once this is done, you shouldn't need the Left$ function.

3) If the field contains Time as well as the Date and all you want is the
date, try Format instead of Left$. Format also returns a text value.
CDate(Format(IIf([Next Evaluation]>[Next Report Due],[Next Report Due],[Next
Evaluation]), "Short Date"))

4) If you don't use the US date format (mm/dd/yyyy) then you will need to
format your dates in this fashion for the query. Also, I recommend that you
use 4 digit years, not 2 digit.

5) You may need to replace Between with

=[Forms]![frmName]![txtStartdate] And <=[Forms]![frmName]![txtEndDate]

or possibly also add date delimiters

="#" & [Forms]![frmName]![txtStartdate] & "#" And <= "#" &

[Forms]![frmName]![txtEndDate] & "#"

--
Wayne Morgan
Microsoft Access MVP

I checked that and no difference. In my calculating field I pick from two

different columns to get the 'highest' date. My code is:

LatestDate: Left$(IIf([Next Evaluation]>[Next Report Due],[Next Report Due],[Next Evaluation]),8)

Where Next Evaluation and Next Report Due are text fields with 255 in

length (that's the reason why for the Left$ function.

For the criteria I want the "LatestDate" to be: between [StartDate] and

[EndDate]. I even tried just a simple [enter date] for the criteria and get
the same results.

 

[Allen Browne]

As I explained, Katie, I would not bother with trying to take that approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen. I know how to do the DateSerial stuff but get a little lost

on trying the rest of the steps within the query. If you could expand a
little bit maybe it will make more sense to me. Thanks for your help!

 

[×ÉÔÁÌÉÊ]

I checked that and no difference. In my calculating field I pick from two

different columns to get the 'highest' date. My code is:

LatestDate: Left$(IIf([Next Evaluation]>[Next Report Due],[Next Report Due],[Next Evaluation]),8)

Where Next Evaluation and Next Report Due are text fields with 255 in

length (that's the reason why for the Left$ function.

For the criteria I want the "LatestDate" to be: between [StartDate] and

[EndDate]. I even tried just a simple [enter date] for the criteria and get
the same results.