Unable to select off of Expr1 field in Access97

  • Thread starter Thread starter Cameron Sutherland
  • Start date Start date
C

Cameron Sutherland

I can't reproduce your error but did you name that column
Expr1 or is it called that because you dropped down the
same field twice? If you include the same field twice
Access may be hanging up on what to do with the output.
Try defining a name for that field yourself (e.g
MonthYear: [datDate])

Regardless of that your like criteria will not work with
dates. Like is only helpfull if you awnt to pass a partial
value and look in your recrodset for that partial value
but in a date field your criterias must have valid dates
so you can't pass partials. Here are 2 ways that you could
get July 2002 from your record set.

1. Put a date range in the criteria of your date field.
CRITERIA=Between #7/1/2002# And #7/31/2002#
2. New column that extracts out month and year only and
critera to show only certain month and year.
FIELD=MonthYear: Format([datDate],"mmyyyy")
CRITERIA=072002
-Cameron Sutherland
 
Hello again, and thanks for your help.

OK, I renamed the column from what Access names it which
is Expr1 to datDate as you suggested.

I am not using the keyword 'like'.

I am simply putting in a normal selection 'Criteria'

=#07/14/2003# just to try to select only those records
with a date of 07/14/2003.

I am still getting the same error.

Here is the SQL Access creates when I try that.

SELECT dbo_Toc.Name, CDate(LTrim(Mid([dbo_Toc]![Name],InStr
([dbo_Toc]![Name],'/')-2,10))) AS datDate
FROM dbo_TD19 INNER JOIN dbo_Toc ON dbo_TD19.TocId =
dbo_Toc.TocId
WHERE (((CDate(LTrim(Mid([dbo_Toc]![Name],InStr([dbo_Toc]!
[Name],'/')-2,10))))=#7/14/2003#) AND ((dbo_TD19.[PRINT
OPTION])='D'));


Any ideas?
-----Original Message-----
I can't reproduce your error but did you name that column
Expr1 or is it called that because you dropped down the
same field twice? If you include the same field twice
Access may be hanging up on what to do with the output.
Try defining a name for that field yourself (e.g
MonthYear: [datDate])

Regardless of that your like criteria will not work with
dates. Like is only helpfull if you awnt to pass a partial
value and look in your recrodset for that partial value
but in a date field your criterias must have valid dates
so you can't pass partials. Here are 2 ways that you could
get July 2002 from your record set.

1. Put a date range in the criteria of your date field.
CRITERIA=Between #7/1/2002# And #7/31/2002#
2. New column that extracts out month and year only and
critera to show only certain month and year.
FIELD=MonthYear: Format([datDate],"mmyyyy")
CRITERIA=072002
-Cameron Sutherland
-----Original Message-----
Hello,

(I am a novice in MS Access97).

In the query designer, when I run a query here is my 2
column result set (Name, Expr1):

Name Expr1
Fx 14647 7/14/2003 4:58:16 PM 07/14/2003
Fx 15162 7/15/2003 1:19:33 PM 07/15/2003

There are 12,000 rows that are returned with all kinds of
dates in my Expr1 column.

My goal is to only return dates in July 2003 that appear
in the Expr1 column.

No matter what I put in the 'Criteria' (like
=#07/14/2003#) I get an error that says 'Invalid Procedure
Call'. Can you not do a selection on an expression field?

To get the information for the time being, I just copy all
of it into Excel, and sort the columns to see how many
records I get for July 2003 information.




.
Click to expand...
.
Click to expand...
 
Back
Top