An SQL Statement in VBA not working

[shaneo via AccessMonster.com]

Hi there

I have an SQL statement that I am trying to get working but am having some
problems

This is the statement I am trying to use

strSQL = "SELECT tblMain.* " & _
"FROM tblMain " & _
"WHERE tblMain.TempName like[Forms]![frmSearch]![txtSearch1].
value" & _
"ORDER BY tblMain.[Bkg No],tblMain.[Surname],tblName[TempName]
,tblName[Department]," & _
"tblMain.[Taken By],tblMain.[Reporting To],tblMain[Start
Date],tblMain[End Date]"

When I try to run it tho I get a runtime error 3075.

Can anyone see what I am doing wrong?

How can I also modify the statement to search values that are LIKE they
values I enter into txtSearch1 as I would like to be able to enter e.g Sha
and find all the names that are LIKE Sha e.g Shane Shaun etc etc.


Hope someone can help out

TIA!!

 

[Allen Browne]

Concatenate the value from the text boxes into the string.
Add the wildcard characters to the string.

strSQL = "SELECT tblMain.* " & _
"FROM tblMain " & _
"WHERE tblMain.TempName like """ &
[Forms]![frmSearch]![txtSearch1].value & _
"*"" ORDER BY ...

 

[shaneo via AccessMonster.com]

Hi Allen,

Thanks for that I have managed to get the statement working. I have tried
adding your suggestion for the wild card but to no joy.....

I changed the string around a little here is my updated one.

strSQL = "SELECT tblMain.[bkg no],tblMain.[Surname],tblMain.[TempName],
tblMain.[Department],tblMain.[Taken By],tblMain.[Reporting To],tblMain.[Start
Date],tblMain.[End Date] " & _
"FROM tblMain " & _
"WHERE tblMain.TempName like """ & [Forms]![frmSearch]!
[txtSearch1].Value & _
" * """

Can you see why it isn't working?

Thanks again

Concatenate the value from the text boxes into the string.
Add the wildcard characters to the string.

strSQL = "SELECT tblMain.* " & _
"FROM tblMain " & _
"WHERE tblMain.TempName like """ &
[Forms]![frmSearch]![txtSearch1].value & _
"*"" ORDER BY ...

Hi there

[quoted text clipped - 21 lines]

values I enter into txtSearch1 as I would like to be able to enter e.g Sha
and find all the names that are LIKE Sha e.g Shane Shaun etc etc.

 

[Duane Hookom]

You have extra spaces around your *. Try:
strSQL = "SELECT [bkg no], [Surname], [TempName], [Department], " & _
"[Taken By], [Reporting To], [Start Date], [End Date] " & _
"FROM tblMain " & _
"WHERE TempName like """ &
[Forms]![frmSearch]![txtSearch1].Value & _
"*"""

--
Duane Hookom
MS Access MVP

Hi Allen,

Thanks for that I have managed to get the statement working. I have tried
adding your suggestion for the wild card but to no joy.....

I changed the string around a little here is my updated one.

strSQL = "SELECT tblMain.[bkg no],tblMain.[Surname],tblMain.[TempName],
tblMain.[Department],tblMain.[Taken By],tblMain.[Reporting
To],tblMain.[Start
Date],tblMain.[End Date] " & _
"FROM tblMain " & _
"WHERE tblMain.TempName like """ & [Forms]![frmSearch]!
[txtSearch1].Value & _
" * """

Can you see why it isn't working?

Thanks again

Concatenate the value from the text boxes into the string.
Add the wildcard characters to the string.

strSQL = "SELECT tblMain.* " & _
"FROM tblMain " & _
"WHERE tblMain.TempName like """ &
[Forms]![frmSearch]![txtSearch1].value & _
"*"" ORDER BY ...

Hi there

[quoted text clipped - 21 lines]

values I enter into txtSearch1 as I would like to be able to enter e.g
Sha
and find all the names that are LIKE Sha e.g Shane Shaun etc etc.