How to change this SQL statement?

[Guest]

Hi!
I am new in SQL, here is a statement. Now I have to select a ID first, I
wish to select a range of it instead. How should I change the code?

Old one
strSel= "Select [DataDate] " & _
" From [Data] " & _
" Where [ID] = '" & Me.SelectedID.Value & "' And " & _
" [ActionDate] Between #" & Me.EndDate.Value & "# And "
& _
" #" & Me.StartDate.Value & "#" & _
" Order by [ActionDate] DESC"
New one
strSel = "Select [DataDate] " & _
" From [Data] " & _
" Where [ID] Between #0# And " & _
" #999# " And & _
" [ActionDate] Between #" & Me.EndDate.Value & "# And "
& _
" #" & Me.StartDate.Value & "#" & _
" Order by [ActionDate] DESC"

I think I have miss something in 4th line. But I not sure what is the
correct stynx.

Thank you very much.

fox

 

[SusanV]

What's the datatype of ID? If it's numeric, no quotes. If Date, use #. If
text, use '

These 2 lines you have # delimiters rather than ' as did the first statement
(leading me to believe the field is text).

" Where [ID] Between #0# And " & _
" #999# " And & _

Try:
strSel = "Select [DataDate] " & _
" From [Data] " & _
" Where [ID] Between '0' And " & _
" '999' " And & _
" [ActionDate] Between #" & Me.EndDate.Value & "# And "
& _
" #" & Me.StartDate.Value & "#" & _
" Order by [ActionDate] DESC"

Not sure you can use Between with a text field, though.
--
hth,
SusanV

Hi!
I am new in SQL, here is a statement. Now I have to select a ID first, I
wish to select a range of it instead. How should I change the code?

Old one
strSel= "Select [DataDate] " & _
" From [Data] " & _
" Where [ID] = '" & Me.SelectedID.Value & "' And " & _
" [ActionDate] Between #" & Me.EndDate.Value & "# And "
& _
" #" & Me.StartDate.Value & "#" & _
" Order by [ActionDate] DESC"
New one
strSel = "Select [DataDate] " & _
" From [Data] " & _
" Where [ID] Between #0# And " & _
" #999# " And & _
" [ActionDate] Between #" & Me.EndDate.Value & "# And "
& _
" #" & Me.StartDate.Value & "#" & _
" Order by [ActionDate] DESC"

I think I have miss something in 4th line. But I not sure what is the
correct stynx.

Thank you very much.

fox

 

[Guest]

Got it. Thank you very much.

What's the datatype of ID? If it's numeric, no quotes. If Date, use #. If
text, use '

These 2 lines you have # delimiters rather than ' as did the first statement
(leading me to believe the field is text).

" Where [ID] Between #0# And " & _
" #999# " And & _

Try:
strSel = "Select [DataDate] " & _
" From [Data] " & _
" Where [ID] Between '0' And " & _
" '999' " And & _
" [ActionDate] Between #" & Me.EndDate.Value & "# And "
& _
" #" & Me.StartDate.Value & "#" & _
" Order by [ActionDate] DESC"

Not sure you can use Between with a text field, though.
--
hth,
SusanV

Hi!
I am new in SQL, here is a statement. Now I have to select a ID first, I
wish to select a range of it instead. How should I change the code?

Old one
strSel= "Select [DataDate] " & _
" From [Data] " & _
" Where [ID] = '" & Me.SelectedID.Value & "' And " & _
" [ActionDate] Between #" & Me.EndDate.Value & "# And "
& _
" #" & Me.StartDate.Value & "#" & _
" Order by [ActionDate] DESC"
New one
strSel = "Select [DataDate] " & _
" From [Data] " & _
" Where [ID] Between #0# And " & _
" #999# " And & _
" [ActionDate] Between #" & Me.EndDate.Value & "# And "
& _
" #" & Me.StartDate.Value & "#" & _
" Order by [ActionDate] DESC"

I think I have miss something in 4th line. But I not sure what is the
correct stynx.

Thank you very much.

fox