J
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
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