SQL Help

[doodle]

greetings all.
windows xp, access 97, visual studio 2005

i can't get this query to work in access. it works in visual studio. I
tried changing out the % with * and I still can't get it to work.

SELECT DISTINCT tblSpindleUsage.PartNum, tblSpindleUsage.PartDescrip,
tbl_JobLog_Data.[Spindle Type]
FROM tblSpindleUsage, tbl_JobLog_Data
WHERE (tblSpindleUsage.PartDescrip LIKE '%' &
tbl_JobLog_Data.[Spindle Type] & '%') AND (tblSpindleUsage.PartNum LIKE
'%' & '*' & '%')
ORDER BY tblSpindleUsage.PartNum


-doodle

 

[Van T. Dinh]

If '%' is the wildcard character in the SQL you posted then what is the '*'?

Do you mean you search for the explict '*' character?

Is the SQL / Query executeed through the QBE or DAO code or ADO code?

 

[Smartin]

greetings all.
windows xp, access 97, visual studio 2005

i can't get this query to work in access. it works in visual studio. I
tried changing out the % with * and I still can't get it to work.

SELECT DISTINCT tblSpindleUsage.PartNum, tblSpindleUsage.PartDescrip,
tbl_JobLog_Data.[Spindle Type]
FROM tblSpindleUsage, tbl_JobLog_Data
WHERE (tblSpindleUsage.PartDescrip LIKE '%' &
tbl_JobLog_Data.[Spindle Type] & '%') AND (tblSpindleUsage.PartNum LIKE
'%' & '*' & '%')
ORDER BY tblSpindleUsage.PartNum

If you are searching literally for "*" try altering your query:

replace '*' with CHR(42)

replace '%' with '*'

 

[John Vinson]

i can't get this query to work in access. it works in visual studio. I
tried changing out the % with * and I still can't get it to work.

If - as I guess, but am not certain! - you're searching for records
with PartDescrip containing the value of [Spindle Type] anywhere
within the field, and PartNum containing an asterisk, try

SELECT DISTINCT tblSpindleUsage.PartNum, tblSpindleUsage.PartDescrip,
tbl_JobLog_Data.[Spindle Type]
FROM tblSpindleUsage, tbl_JobLog_Data
WHERE (tblSpindleUsage.PartDescrip LIKE '*' &
tbl_JobLog_Data.[Spindle Type] & '*') AND (tblSpindleUsage.PartNum
LIKE '*[*]*')
ORDER BY tblSpindleUsage.PartNum;

The asterisk is a wildcard character for JET queries, so it must be
made literal by putting it in square brackets.

Note that this query will not be updateable, and may be very slow
since you won't get the advantage of any indexes on the searches.

John W. Vinson[MVP]

 

[doodle]

Thank you John, that is exactly what I was looking for. Now how can I
add a count of spindle type to the query? I tried this, but it doesn't
work in access.

I got a Syntax Error - missing operator.

SELECT DISTINCT tblSpindleUsage.PartNum, tblSpindleUsage.PartDescrip,
tbl_JobLog_Data.[Spindle Type],Count(tbl_JobLog_Data.[Spindle Type]
FROM tblSpindleUsage, tbl_JobLog_Data
WHERE (tblSpindleUsage.PartDescrip LIKE '*' &
tbl_JobLog_Data.[Spindle Type] & '*') AND (tblSpindleUsage.PartNum
LIKE '*[*]*')
ORDER BY tblSpindleUsage.PartNum;



-doodle

 

[doodle]

I fixed it with this:

SELECT DISTINCT tblSpindleUsage.PartNum, tblSpindleUsage.PartDescrip,
tbl_JobLog_Data.[Spindle Type], Count(tbl_JobLog_Data.[Spindle Type])
AS [CountOfSpindle Type]
FROM tblSpindleUsage, tbl_JobLog_Data
GROUP BY tblSpindleUsage.PartNum, tblSpindleUsage.PartDescrip,
tbl_JobLog_Data.[Spindle Type]
HAVING (((tblSpindleUsage.PartNum) Like '*[*]*') AND
((tblSpindleUsage.PartDescrip) Like '*' & [tbl_JobLog_Data].[Spindle
Type] & '*'))
ORDER BY tblSpindleUsage.PartNum;


Thanks all for your help.

-doodle