User Selection - SQL query

[VS24]

Help on a query.
Using a newly created column - would like to pop in data depending on the selection made by the user.

eg. New field = AgeCategory (Class 1 are < 13yrs, Class 2 are between 13 and 18 yrs and Class 3 are > 18yrs!)

In the combo box when they select < 13yrs, it should pop in "Class 1" etc.

The SQL code to do this...

Thanx!

 

[TheAceMan1]

How are ya VS24 . . . . .

[blue]Using a newly created column . . .[/blue]

[purple]Created where?[/purple]

[blue]Whatexactly does the data look like in the column your using in the combobox?[/blue]

[blue]1yr, 2yr . . . 18yr, 19yr . . .
1, 2, . . . 18, 19, . . .[/blue]

What?

See Ya! . . . . . .

 

[VS24]

I'm kewl!

Table called People. Added a new column called AgeCategory.

When the user selects from a combo box with options
"< 12yrs" or "Between 12 and 18 years" or "18 and older"
it should pop in either Class 1, 2 or 3 respectively!

Need to know the SQL code!


Thanx!

 

[VS24]

PLUS... ALSO... I have information in a table already and if I want to concatenate two fields and pop them into another field... what would the SQL query for that look like??

Thanx!

 

[RoyVidar]

Why on earth would you like to concatenate two fields and store them? That will must certainly give you headaches later on. You should only concatenate them on the fly whenever you need to display them, not store values that can be derived from other fields.

Shouldn't be any need for SQL to enter the category. In your form where the user selects the category, simply bind the combo to the the AgeCategory field. (I am assuming you've used the combo wizard to create the combo, and that it is based on a lookup table in case you need to expand on the classes later on,, where the first column, the bound column, contains the class number (1, 2, 3), the second column, the displayed column, the class description)

Roy-Vidar

 

[Tranman]

I'd add a second column to the combo, so it is like:
<13yrs |Class1
13-18yrs |Class2
>18yrs |Class3

You could hide the second column (or not...)

SQL:
CurrentProject.Connection.Execute "Insert into <tablename>(<fieldname>) values('" & Forms!<formname>!<comboname>.Column(1) & "')"

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant."
Mark Twain

 

[TheAceMan1]

OK VS24 . . . . .

In the AfterUpdate event of the combobox, copy/paste the following ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim Yrs As String
   
   cmpTxt = Me![purple][b]ComboboxName[/b][/purple].Column(1)
   
   If Yrs = "< 12yrs" Then
      AgeCategory = "Class 1"
   ElseIf Yrs = "Between 12 and 18 years" Then
      AgeCategory = "Class 2"
   Else
      AgeCategory = "Class 3"
   End If[/blue]

Don't know which column your using in the combobox, so may have to play with the Column([purple]x[/purple]).

[blue]I want to concatenate two fields and pop them into another field... what would the SQL query for that look like??[/blue]

In query design view, in a new field on the field row:

[blue][purple][b]NewColumnName[/b][/purple]:[purple][b]Field1[/b][/purple] & " " & [purple][b]Field2[/b][/purple][/blue]

See Ya! . . . . . .