|
|
|
Calculated Field with Criteria? |
|---|
| message from =?Utf-8?B?S2F0aWU=?= on 3 Jun 2004 |
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!
|
| anonymous replied to =?Utf-8?B?S2F0aWU=?= on 3 Jun 2004 |
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!
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
|
| =?Utf-8?B?S2F0aWU=?= replied to anonymous on 3 Jun 2004 |
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.
|
| Wayne Morgan replied to =?Utf-8?B?S2F0aWU=?= on 3 Jun 2004 |
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
or possibly also add date delimiters
[Forms]![frmName]![txtEndDate] & "#"
|
| Allen Browne replied to =?Utf-8?B?S2F0aWU=?= on 3 Jun 2004 |
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.
|
| =?Utf-8?B?S2F0aWU=?= replied to Allen Browne on 3 Jun 2004 |
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!
|
| Allen Browne replied to =?Utf-8?B?S2F0aWU=?= on 3 Jun 2004 |
As I explained, Katie, I would not bother with trying to take that approach.
|
| ΧΙΤΑΜΙΚ replied to =?Utf-8?B?S2F0aWU=?= on 5 Jun 2004 |
different columns to get the 'highest' date. My code is:
Due],[Next Evaluation]),8)
length (that's the reason why for the Left$ function.
[EndDate]. I even tried just a simple [enter date] for the criteria and get
the same results.
|
|
