An SQL Statement in VBA not working

  • Thread starter Thread starter shaneo via AccessMonster.com
  • Start date Start date
S

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!!
 
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 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
Allen said:
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
Click to expand...
[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.
Click to expand...
Click to expand...
 
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


shaneo via AccessMonster.com said:
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
Allen said:
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
Click to expand...
[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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top