Why?

[G Lam]

Hi, I have the following code for a Form, but when I click on the button, it
pops up MSG boxes asking for FirstStr and LastStr. The data in the resulting
table are right (between and include the FirstStr and LastStr). I don't
understand why it asks for input, because I had hard coded them in the SUB.
Although, what I finally would want to do is ask for user input, but I need
to write some more codes to limit the input range. Here are the code:
Thank you in advance.
Gary

Private Sub Maketable_Click()
On Error GoTo Err_Maketable_Click
Dim FirstStr As String
Dim LastStr As String
FirstStr = "987654327"
LastStr = "987654333"


DoCmd.RunSQL "Select TblBsd.Barcode, Tblbsd.Description INTO [BSDBak]" _
& "From TBLBSD Where TBLBSD.Barcode Between """"&FirstStr&"""" And
""""&LastStr&"""";"

Exit_Maketable_Click:
Exit Sub

Err_Maketable_Click:
MsgBox Err.Description
Resume Exit_Maketable_Click

End Sub

 

[Ken Snell]

Assuming that you've posted the code exactly as it is in your module, you're
missing blank spaces on either side of the & signs, and you have one two
many " characters in each grouping:

DoCmd.RunSQL "Select TblBsd.Barcode, Tblbsd.Description INTO [BSDBak]" _
& "From TBLBSD Where TBLBSD.Barcode Between """ & FirstStr & """ And
""" & LastStr & """;"

 

[G Lam]

Ken,
Thank you very much. It worked. I always get confused by this " thing.
Some times, I can use only one " on each side, some times, two or more. How
can I determine how many " should be used?
Thanks again.
Gary

Assuming that you've posted the code exactly as it is in your module, you're
missing blank spaces on either side of the & signs, and you have one two
many " characters in each grouping:

DoCmd.RunSQL "Select TblBsd.Barcode, Tblbsd.Description INTO [BSDBak]" _
& "From TBLBSD Where TBLBSD.Barcode Between """ & FirstStr & """ And
""" & LastStr & """;"


--
Ken Snell
<MS ACCESS MVP>

Hi, I have the following code for a Form, but when I click on the

button,

it
pops up MSG boxes asking for FirstStr and LastStr. The data in the resulting
table are right (between and include the FirstStr and LastStr). I don't
understand why it asks for input, because I had hard coded them in the SUB.
Although, what I finally would want to do is ask for user input, but I need
to write some more codes to limit the input range. Here are the code:
Thank you in advance.
Gary

Private Sub Maketable_Click()
On Error GoTo Err_Maketable_Click
Dim FirstStr As String
Dim LastStr As String
FirstStr = "987654327"
LastStr = "987654333"


DoCmd.RunSQL "Select TblBsd.Barcode, Tblbsd.Description INTO

[BSDBak]"

_
& "From TBLBSD Where TBLBSD.Barcode Between """"&FirstStr&"""" And
""""&LastStr&"""";"

Exit_Maketable_Click:
Exit Sub

Err_Maketable_Click:
MsgBox Err.Description
Resume Exit_Maketable_Click

End Sub

 

[Ken Snell]

You always use one " when you are starting or ending a string.

You always use two " when you want to insert a " character into a string
(two " characters in a row are read as a single " character in the string).

Thus, in your situation, you are putting a " character as a delimiter around
the variable's value. As such, these delimiting " characters are at the end
and at the beginning of your string. Therefore, you use three " characters
to get one " character inserted and to end/start the string.

You use four " characters when you want to insert a " character into a text
string and there are no other characters on either side. Therefore, one "
starts the string, two " characters insert a " character, and one "
character ends the string.

--
Ken Snell
<MS ACCESS MVP>

Ken,
Thank you very much. It worked. I always get confused by this " thing.
Some times, I can use only one " on each side, some times, two or more. How
can I determine how many " should be used?
Thanks again.
Gary

Assuming that you've posted the code exactly as it is in your module, you're
missing blank spaces on either side of the & signs, and you have one two
many " characters in each grouping:

DoCmd.RunSQL "Select TblBsd.Barcode, Tblbsd.Description INTO [BSDBak]" _
& "From TBLBSD Where TBLBSD.Barcode Between """ & FirstStr & """ And
""" & LastStr & """;"


--
Ken Snell
<MS ACCESS MVP>

Hi, I have the following code for a Form, but when I click on the

button,

it
pops up MSG boxes asking for FirstStr and LastStr. The data in the resulting
table are right (between and include the FirstStr and LastStr). I don't
understand why it asks for input, because I had hard coded them in the SUB.
Although, what I finally would want to do is ask for user input, but I need
to write some more codes to limit the input range. Here are the code:
Thank you in advance.
Gary

Private Sub Maketable_Click()
On Error GoTo Err_Maketable_Click
Dim FirstStr As String
Dim LastStr As String
FirstStr = "987654327"
LastStr = "987654333"


DoCmd.RunSQL "Select TblBsd.Barcode, Tblbsd.Description INTO

[BSDBak]"

_
& "From TBLBSD Where TBLBSD.Barcode Between """"&FirstStr&"""" And
""""&LastStr&"""";"

Exit_Maketable_Click:
Exit Sub

Err_Maketable_Click:
MsgBox Err.Description
Resume Exit_Maketable_Click

End Sub

 

[Jeerakarn W.]

Hi.

I always confuse with "" or " or """, It may be four single quote. To make
it easier to read the code.
It better to use CHR(34) instead of " like this.

vbDQ = chr(34)

DoCmd.RunSQL "Select TblBsd.Barcode, Tblbsd.Description INTO [BSDBak] " _
& " From TBLBSD Where TBLBSD.Barcode Between " & vbDQ & FirstStr & vbDQ
_
& " And " & vbDQ & LastStr & vbDQ


Jeerakarn Watakit

Ken,
Thank you very much. It worked. I always get confused by this " thing.
Some times, I can use only one " on each side, some times, two or more. How
can I determine how many " should be used?
Thanks again.
Gary

Assuming that you've posted the code exactly as it is in your module, you're
missing blank spaces on either side of the & signs, and you have one two
many " characters in each grouping:

DoCmd.RunSQL "Select TblBsd.Barcode, Tblbsd.Description INTO [BSDBak]" _
& "From TBLBSD Where TBLBSD.Barcode Between """ & FirstStr & """ And
""" & LastStr & """;"


--
Ken Snell
<MS ACCESS MVP>

Hi, I have the following code for a Form, but when I click on the

button,

it
pops up MSG boxes asking for FirstStr and LastStr. The data in the resulting
table are right (between and include the FirstStr and LastStr). I don't
understand why it asks for input, because I had hard coded them in the SUB.
Although, what I finally would want to do is ask for user input, but I need
to write some more codes to limit the input range. Here are the code:
Thank you in advance.
Gary

Private Sub Maketable_Click()
On Error GoTo Err_Maketable_Click
Dim FirstStr As String
Dim LastStr As String
FirstStr = "987654327"
LastStr = "987654333"


DoCmd.RunSQL "Select TblBsd.Barcode, Tblbsd.Description INTO

[BSDBak]"

_
& "From TBLBSD Where TBLBSD.Barcode Between """"&FirstStr&"""" And
""""&LastStr&"""";"

Exit_Maketable_Click:
Exit Sub

Err_Maketable_Click:
MsgBox Err.Description
Resume Exit_Maketable_Click

End Sub