SQL Help

[vix666]

I have the code shown below which looks up 2 values in another table and inserts those two values into my form. But these is a problem with the sql statement. I can get it to work if i just use "select * from tbl_yearly_figures", but when I put in the "where" part then it comes up with a data type mismatch error - any help would be appreciated.

Thanks in advance

Vicky


Private Sub cbo_kpi_id_AfterUpdate()

Dim SQL As String
Dim SQLResult As Integer
Dim db As Database
Dim rs As Recordset
Dim strPrevYrAct As String, strYearTgt As String

cbo_kpi_id.SetFocus

SQL = "SELECT * from tbl_yearly_figures where kpi_id = '" & Me!cbo_kpi_id & "' ;"

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenDynaset)

strPrevYrAct = rs!prev_yr_act
strYearTgt = rs!year_tgt

txt_prev_yr_act.SetFocus
txt_prev_yr_act = strPrevYrAct
txt_year_tgt.SetFocus
txt_year_tgt = strYearTgt

End Sub

 

[KenReay]

Hi

The way you have teh SQL:

SQL = "SELECT * from tbl_yearly_figures where kpi_id = '" & Me!cbo_kpi_id & "' ;"

implies kpi_id is a string, is it?

If it is a number (eg autonumber, long etc) you need to drop the quotes so

SQL = "SELECT * from tbl_yearly_figures where kpi_id = " & Me!cbo_kpi_id & " ;"

if it is a date you need # marks so:

SQL = "SELECT * from tbl_yearly_figures where kpi_id = #" & Me!cbo_kpi_id & "# ;"

note for dates you may also need a format() command, but from the name of teh variable I assume it is a number



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RoyVidar]

Hi!

If your control is text, which i presume since it bears sign of being a combo, you're right about the text qualifiers ('), but you dont need the semicolon:

SQL = "SELECT * from tbl_yearly_figures where kpi_id = '" & Me!cbo_kpi_id & "'"

If the control is numeric, you don't need any qualifiers at all

SQL = "SELECT * from tbl_yearly_figures where kpi_id = " & Me!cbo_kpi_id

BTW - you should'nt need to setfocus to the control

But - also check whether the control has a value!

If len(me!cbo_kpi_id)>0 then
run your code
else
msgbox "bla bla"
endif

HTH Roy-Vidar

 

[vix666]

Thanks alot for your help,it was a number so i removed the quotes and it worked

Thanks again