Updating a field after OnUpdate event

[ChimpZter]

Hi

I have a form with a comboBox, the user selects a value and I wish to update a field on a filtered form with the following SQL statement:

"SELECT tbl_ServiceIntervals.[275to400] " & _
"FROM tbl_ServiceIntervals " & _
"WHERE (((tbl_ServiceIntervals.[Style/Spec])=""930""))"

it will only ever return one value.

as you can gather I am a newbie, thanks
John

 

[DCrake]

Simple Software Solutions

Hi

Your code is a little worrying. first of all you need to read up on Naming Conventions for fields in tables. Using certain characters can reek havoc when coding later on.

"SELECT tbl_ServiceIntervals.[275to400] " & _
"FROM tbl_ServiceIntervals " & _
"WHERE (((tbl_ServiceIntervals.[Style/Spec])=""930""))"

Should Read

"SELECT [275to400] FROM tbl_ServiceIntervals WHERE [Style/Spec]= 930"

The only reason why I am using [] is that Access can easily get confused with field names, epsecially those starting with a number or those containing unusual characters like / key.

When retrieving data from a single table you only need to declare it once in the FROM statement.

In the revised code it expects that the style/spec data is numeric so it does not need quotes around it. If it is a string then you would encompass it with single quotes.

However if the criteria (930) is being passed to the SQL as a variable then the command would be as follows:

Numeric value:

"SELECT [275to400] FROM tbl_ServiceIntervals WHERE [Style/Spec]= " & IntStyle

OR

String value:

"SELECT [275to400] FROM tbl_ServiceIntervals WHERE [Style/Spec]= '" & StrStyle & "'"

If you do not declare them correctly you may encounter the error 'Data Type Mismatch' problem

Code Master:http://www.icraftlimited.co.uk

 

[ChimpZter]

Thanks for that