Does inputBox has "vbCancelOK" value as msgBox? Thanks

[xpengi]

Hi guys,

I want to give user a prompt(inputBox) to input value of a field nam
of table

Private Sub form_open(Cancel As Integer)
On Error GoTo Err_Form_Open
...
Dim prompt As String
Dim inCity As String
Dim inAnswer As Integer

prompt = "Please input the CITY name:"
inCity = InputBox(prompt, "CITY")

intAnswer = ?????? <---how to code this?
Question1

If intAnswer = vbCancel Then
Cancel = 1
Exit Sub
ElseIf intAnswer = vbOK Then
strSQL = "SELECT CITY, COUNT(*) AS [NUMBER OF STUDENTS] FRO
" _
& Tb & " WHERE CITY = '" & _
inCity & "' GROUP BY CITY;"
End If

......



BTW,

Querstion2 ---how to make the input value is not case sensitive whe
query the table data?

Querstion2 ---is there a way to check the input value's validity base
on the field's value in table?

I am novice, please give me details code. Thank you very much!

 

[Leif]

The the user presses cancel an empty string is return, so you can test for
that (it will be inCity).

Access is not case sensitive.

You might consider place your prompt for city as a text box on your form.

 

[xpengi]

the table is linked one of a Oracle table. when i query " Toronto" an
"toronto", i will get different result return.

how to solve it? Thank

 

[Leif]

OK, Oracle is definiately case sensitive.

What you need to do is make your sql command a "case insensitive" search.
Try something like the following:

strSQL = "SELECT CITY, COUNT(*) AS [NUMBER OF STUDENTS] FROM
" _
& Tb & " WHERE UPPER(CITY) = '" & _
ucase(inCity) & "' GROUP BY CITY;"

 

[xpengi]

not work

 

[Leif]

The format "[NUMBER OF STUDENTS]" is access, and will not work with Oracle.
For a column alias in Oracle you need to use either NUMBER_OF_STUDENTS or
within double quotes "Number of Students". For example,

strSQL = "SELECT CITY, COUNT(*) AS NUMBER_OF_STUDENTS FROM
" _
& Tb & " WHERE UPPER(CITY) = '" & _
ucase(inCity) & "' GROUP BY CITY;"