Query Problem

[Jeremy Kropf]

I have a table with a field memo field 'groups'
containing information as to what group a person belongs
to. Multiples are allowed. A sample entry would
be "SS1^09030804". The values after the caret character
are beginning and ending date values (0903 being
September '03 etc.) [This is not the way I would have set
it up, but what I have to work with for now].

I want to select all the people in a certain group, who
end after a certain date. based on the values I select in
a form. I have tried to use two queries to do this, but I
can't seem to get it to work.

First I selected everyone meeting the group criteria,
regardless of date:

myIndSQL = "SELECT IndRec.IndivNo, IndRec.LastName,
IndRec.FirstName, IndRec.Groups" _
& " FROM IndRec WHERE (((IndRec.Groups) Like '*" &
Me!GroupSelected & "*')) ORDER BY IndRec.LastName;"
Set myInd = myDB.CreateQueryDef("MyIndQuery", myIndSQL)

This code works just fine. It selects all the people who
have a certain group. The second code is what I have the
problem with. I wanted to try to narrow the first
selection to only those whose end date is after the
criteria selected.

I tried this:

myDateSQL = "SELECT * FROM myIndQuery" _
& " WHERE (((cdate(Mid([Groups], InStr([Groups], '" &
Me!GroupSelected & "') + 8, 2)/1/" _
& "Mid([Groups], InStr([Groups], '" & Me!
GroupSelected & "') + 10, 2))" _
& ">=#" & (Left$(Me!FirstDate, 2) & "/1/" & Right$(Me!
FirstDate, 2)) & "#)));" _

Set myDate = myDB.CreateQueryDef("MyDateQuery", myDateSQL)

When I open this query it gives me a "Data Type Mismatch
in criteria expression" error. I have tried to pick this
apart, and it seems as though the error comes in when I
try to use the InStr function, but I am not sure how else
to get the end date of the group I am looking for. Maybe
there is a lot easier way.

Help will be very much appreciated.

Thanks.
Jeremy

 

[[MVP] S. Clark]

1. Find the original developer and plonk him on the forhead with whatever is
within reach.
2. Convert the data to a useful data structure, as this will be a continuing
nightmare until the app dies.

FWIW, datatype mismatches happen when you try to compare two datatypes that
don't match. Typically, a Null is the problem, so use NZ() to prevent the
error.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-