A97 SQL in VBA - Almost Hairless Now!

[Kahuna]

Hi Folks - once again the Access SQL Syntax with regard to 'Where' clause
has me stumped.

I have an SQL definition in my code as follows:
==============================================
strSQLSelectRecs = "SELECT qryFabricCondition.fc_id,
tblImages.img_fc_rec_id, tblImages.img_filename, File_Path([img_filename])
AS Path, File_Name([img_filename]) AS File" _
& " FROM qryFabricCondition LEFT JOIN tblImages ON
qryFabricCondition.fc_id = tblImages.img_fc_rec_id" _
& " WHERE File_Path([img_filename])='strOldPath'"
==============================================
Which gives me an error that there is no current record. However the same
query using the Query Form as follows:
==============================================
SELECT qryFabricCondition.fc_id, tblImages.img_fc_rec_id,
tblImages.img_filename, File_Path([img_filename]) AS Path,
File_Name([img_filename]) AS File
FROM qryFabricCondition LEFT JOIN tblImages ON qryFabricCondition.fc_id =
tblImages.img_fc_rec_id
WHERE
(((File_Path([img_filename]))=[Forms]![frm_ChangeImageDocumentPath]![cmbOldPath]));
==============================================
Which is the same except it uses the explicit link to 'form' content (which
is the same as the declared variable strOldPath), it reports two records
found (correct).

File_Path and File_Name parses the path and filename respectively. I have
File_Path shown as 'Path' also but I cant get the system to accept that as a
valid field from the query in recordset building.

What's the best route to get this where clause running from code using this
variable.

Appreciate any feedback

 

[Rick Brandt]

Hi Folks - once again the Access SQL Syntax with regard to 'Where'
clause has me stumped.

I have an SQL definition in my code as follows:
==============================================
strSQLSelectRecs = "SELECT qryFabricCondition.fc_id,
tblImages.img_fc_rec_id, tblImages.img_filename,
File_Path([img_filename]) AS Path, File_Name([img_filename]) AS File"
_ & " FROM qryFabricCondition LEFT JOIN tblImages ON
qryFabricCondition.fc_id = tblImages.img_fc_rec_id" _
& " WHERE File_Path([img_filename])='strOldPath'"

Your SQL string (when parsed) will contain the name of the variable instead of
the value of the variable and variables cannot be evaluated in queries.

You need to delimit out the variable like...

& " WHERE File_Path([img_filename])='" & strOldPath & "'"

 

[Allen Browne]

Concatenate the path name into the string:

strSQLSelectRecs = "SELECT qryFabricCondition.fc_id,
tblImages.img_fc_rec_id, tblImages.img_filename, File_Path([img_filename])
AS Path, File_Name([img_filename]) AS File" _
& " FROM qryFabricCondition LEFT JOIN tblImages ON
qryFabricCondition.fc_id = tblImages.img_fc_rec_id" _
& " WHERE File_Path([img_filename])='" & strOldPath & "';"

 

[Kahuna]

Your SQL string (when parsed) will contain the name of the variable
instead of the value of the variable and variables cannot be evaluated in
queries.

You need to delimit out the variable like...

& " WHERE File_Path([img_filename])='" & strOldPath & "'"

I knew that... lol - day wouldn't have been complete without something
really stoopid coming out of me!

Thanks Rick - helpful as ever!

 

[Kahuna]

Thanks Allen - Rick gave the same advice - much appreciated.

Concatenate the path name into the string:

strSQLSelectRecs = "SELECT qryFabricCondition.fc_id,
tblImages.img_fc_rec_id, tblImages.img_filename, File_Path([img_filename])
AS Path, File_Name([img_filename]) AS File" _
& " FROM qryFabricCondition LEFT JOIN tblImages ON
qryFabricCondition.fc_id = tblImages.img_fc_rec_id" _
& " WHERE File_Path([img_filename])='" & strOldPath & "';"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Folks - once again the Access SQL Syntax with regard to 'Where' clause
has me stumped.

I have an SQL definition in my code as follows:
==============================================
strSQLSelectRecs = "SELECT qryFabricCondition.fc_id,
tblImages.img_fc_rec_id, tblImages.img_filename,
File_Path([img_filename]) AS Path, File_Name([img_filename]) AS File" _
& " FROM qryFabricCondition LEFT JOIN tblImages ON
qryFabricCondition.fc_id = tblImages.img_fc_rec_id" _
& " WHERE File_Path([img_filename])='strOldPath'"
==============================================
Which gives me an error that there is no current record. However the same
query using the Query Form as follows:
==============================================
SELECT qryFabricCondition.fc_id, tblImages.img_fc_rec_id,
tblImages.img_filename, File_Path([img_filename]) AS Path,
File_Name([img_filename]) AS File
FROM qryFabricCondition LEFT JOIN tblImages ON qryFabricCondition.fc_id =
tblImages.img_fc_rec_id
WHERE
(((File_Path([img_filename]))=[Forms]![frm_ChangeImageDocumentPath]![cmbOldPath]));
==============================================
Which is the same except it uses the explicit link to 'form' content
(which is the same as the declared variable strOldPath), it reports two
records found (correct).

File_Path and File_Name parses the path and filename respectively. I have
File_Path shown as 'Path' also but I cant get the system to accept that
as a valid field from the query in recordset building.

What's the best route to get this where clause running from code using
this variable.

Appreciate any feedback