Trouble with A97 query syntax...

[MLH]

SELECT tblVehicleJobs.SerialNum
FROM tblOwners RIGHT JOIN tblVehicleJobs ON tblOwners.OwnerID =
tblVehicleJobs.OwnerID
WHERE (((tblVehicleJobs.SerialNum) Like "i*"));

The SQL above lists the rows I want (all vehicles whose VIN begins
with the letter "i"). The SQL below does not produce the rows when
I type the letter "i" into [FirstCharOfVIN] control on form named
frmDMV1stLttr_Type_Other.

SELECT tblVehicleJobs.SerialNum
FROM tblOwners RIGHT JOIN tblVehicleJobs ON tblOwners.OwnerID =
tblVehicleJobs.OwnerID
WHERE
(((tblVehicleJobs.SerialNum)=[Forms]![frmDMV1stLttr_Type_Other]![FirstCharOfVIN]
& "*"));
Can someone help me with this query-by-form? What I was hoping is that
[Forms]![frmDMV1stLttr_Type_Other]![FirstCharOfVIN] & "*" would evalu-
ate to "i*" (as in the first query - which works).

[MLH]

Further testing shows the following SQL syntax fails to produce the
desired rows as well. Still struggling with the problem. This may shed
some light on it...

SELECT tblVehicleJobs.SerialNum
FROM tblOwners RIGHT JOIN tblVehicleJobs ON tblOwners.OwnerID =
tblVehicleJobs.OwnerID
WHERE (((tblVehicleJobs.SerialNum)="i" & "*"));

[MLH]

I will use the following work-around, but I am still curious why
the 2 queries in the original post do not return the same dynaset.

Work-around...
SELECT tblVehicleJobs.SerialNum, Left$([SerialNum],1) AS VIN1stChar
FROM tblOwners RIGHT JOIN tblVehicleJobs ON tblOwners.OwnerID =
tblVehicleJobs.OwnerID
WHERE
(((Left$([SerialNum],1))=[Forms]![frmDMV1stLttr_Type_Other]![FirstCharOfVIN]));

[Tim Marshall]

MLH wrote:

WHERE (((tblVehicleJobs.SerialNum) Like "i*"));

WHERE
(((tblVehicleJobs.SerialNum)=[Forms]![frmDMV1stLttr_Type_Other]![FirstCharOfVIN]
& "*"));

There's the difference. If you change the = in the second to Like,
you'll be fine, ie,

WHERE
(((tblVehicleJobs.SerialNum) like
[Forms]![frmDMV1stLttr_Type_Other]![FirstCharOfVIN]
& "*"))

The way you had it, would be the same as if you wrote the query for "i" as:

WHERE (((tblVehicleJobs.SerialNum) = "i*"))

In other words, it was looking for serial numbers that were two
characters long, and were and I and an asterisk.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[MacDermott]

I don't know exactly how you're using this SQL, but I do know that after
executing this line:
MyStr="WHERE tblVehicleJobs.SerialNum LIKE " &
[Forms]![frmDMV1stLttr_Type_Other]![FirstCharOfVIN] & "*"

MyStr will read like this:
WHERE tblVehicleJobs.SerialNum LIKE i*
NOT
WHERE tblVehicleJobs.SerialNum LIKE "i*"

So you might want to look at concatenating some quotes in there...

"MLH" <CR**@NorthState.net> wrote in message
news:pu********************************@4ax.com...

SELECT tblVehicleJobs.SerialNum
FROM tblOwners RIGHT JOIN tblVehicleJobs ON tblOwners.OwnerID =
tblVehicleJobs.OwnerID
WHERE (((tblVehicleJobs.SerialNum) Like "i*"));

The SQL above lists the rows I want (all vehicles whose VIN begins
with the letter "i"). The SQL below does not produce the rows when
I type the letter "i" into [FirstCharOfVIN] control on form named
frmDMV1stLttr_Type_Other.

SELECT tblVehicleJobs.SerialNum
FROM tblOwners RIGHT JOIN tblVehicleJobs ON tblOwners.OwnerID =
tblVehicleJobs.OwnerID
WHERE
(((tblVehicleJobs.SerialNum)=[Forms]![frmDMV1stLttr_Type_Other]![FirstCharOf
VIN] & "*"));
Can someone help me with this query-by-form? What I was hoping is that
[Forms]![frmDMV1stLttr_Type_Other]![FirstCharOfVIN] & "*" would evalu-
ate to "i*" (as in the first query - which works).

[MLH]

Yes. You were correct. Thx much!
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

MLH wrote:

WHERE (((tblVehicleJobs.SerialNum) Like "i*"));

WHERE
(((tblVehicleJobs.SerialNum)=[Forms]![frmDMV1stLttr_Type_Other]![FirstCharOfVIN]
& "*"));

There's the difference. If you change the = in the second to Like,
you'll be fine, ie,

WHERE
(((tblVehicleJobs.SerialNum) like
[Forms]![frmDMV1stLttr_Type_Other]![FirstCharOfVIN]
& "*"))

The way you had it, would be the same as if you wrote the query for "i" as:

WHERE (((tblVehicleJobs.SerialNum) = "i*"))

In other words, it was looking for serial numbers that were two
characters long, and were and I and an asterisk.