Calling variables on SQL

[Garcimat]

Hi guys
how do I call a variable inside a sql query ? I can do it on the sql server but no with access.
I want to use the value from a combo box as filter, actually several combos

Dim sql As String
Dim varCMB As Variant ' value from the combo box
varCMB = Me.cmbSection
sql = "SELECT* FROM tblData WHERE Section = varCMB"

Thanks in advance

 

[CJ_London]

you don't need varCMB

just this if Me.cmbSection is numeric

sql = "SELECT * FROM tblData WHERE Section =" & Me.cmbSection

and this if it is text

sql = "SELECT * FROM tblData WHERE Section ='" & Me.cmbSection & "'"

 

[Garcimat]

you don't need varCMB

just this if Me.cmbSection is numeric

sql = "SELECT * FROM tblData WHERE Section =" & Me.cmbSection

and this if it is text

sql = "SELECT * FROM tblData WHERE Section ='" & Me.cmbSection & "'"

I need to stop and learn all this concatenation.... Thank you

 

[Garcimat]

hey @CJ_London , this is a query that @arnelgp helped me with, but I am doing some modifications...
it is working now but is there a better way to write this ? Looks weird and too long.... having two "AND".

strSQL = "SELECT * FROM tblData WHERE Section ='" & Me.cmbSection & "' AND " & "((" & X & ") Between Kp1 And KP2) Or " & "((" & Y & ") Between Kp1 And KP2) AND Direction= '" & Me.cmbDirection1 & "'" 'sintax for text values

 

[Garcimat]

Actually it is not working, it is not getting the second "AND"

 

[CJ_London]

build the string in stages - check it works at each stage - it can be simplified a bit

strSQL = "SELECT * FROM tblData WHERE Section ='" & Me.cmbSection & "' AND ((" & X & ") Between Kp1 And KP2) Or ((" & Y & ") Between Kp1 And KP2) AND Direction= '" & Me.cmbDirection1 & "'"

based on your sql, Me.cmbSection and Me.cmbDirection1 are text whilst X and Y are numbers

but looks like you are mixing and's and or's so check your brackets

WHERE
Section ='" & Me.cmbSection & "'
AND ((" & X & ") Between Kp1 And KP2) -suspect this should be AND (((" & X & ") Between Kp1 And KP2
Or ((" & Y & ") Between Kp1 And KP2) and this should be Or ((" & Y & ") Between Kp1 And KP2))
AND Direction= '" & Me.cmbDirection1 & "'"

but since I don't know the intent, just a guess -

it could be (section and X) OR (Y and direction)

or as I think per above (section) and (X or Y) and direction

each potentially will give different results

 

[Garcimat]

build the string in stages - check it works at each stage - it can be simplified a bit

strSQL = "SELECT * FROM tblData WHERE Section ='" & Me.cmbSection & "' AND ((" & X & ") Between Kp1 And KP2) Or ((" & Y & ") Between Kp1 And KP2) AND Direction= '" & Me.cmbDirection1 & "'"

based on your sql, Me.cmbSection and Me.cmbDirection1 are text whilst X and Y are numbers
Yes, you are right
but looks like you are mixing and's and or's so check your brackets
Yes, you are right again
WHERE
Section ='" & Me.cmbSection & "'
AND ((" & X & ") Between Kp1 And KP2) -suspect this should be AND (((" & X & ") Between Kp1 And KP2
Or ((" & Y & ") Between Kp1 And KP2) and this should be Or ((" & Y & ") Between Kp1 And KP2))
AND Direction= '" & Me.cmbDirection1 & "'"

but since I don't know the intent, just a guess -

it could be (section and X) OR (Y and direction)
X is the start (Kilometre) and Y is the end (Kilometre) - Section is the rail Line (West - London) - Direction is the Track, most of the time we have one track going to the city (Up Track) and one going away from the city (Down Track)
or as I think per above (section) and (X or Y) and direction

each potentially will give different results

I need to check if the work section X to Y is not been used by another work group, if it is free I issue a Work Permit.... I can not have overlapping ( two groups working in the same work section)

 

[arnelgp]

why is KP2 blank on most records on tblData? i thought there are limits?