By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,407 Members | 2,842 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,407 IT Pros & Developers. It's quick & easy.

Trouble with A97 query syntax...

P: n/a
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).
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" & "*"));

Nov 13 '05 #2

P: n/a
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]));

Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

P: n/a
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).

Nov 13 '05 #5

P: n/a
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.


Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.