Query in a Form (1 Viewer)

[sundaram]

Dear Frinds,

I am very freshner

Iam trying to find Serial No of Employee register.
SlNo is Long Integer (Data Type)

Dim IntSql as integer
Dim StrCri as String
I am trying, it is showing error Run time ‘3464’:
Data type mismatch in criteria expression.


I tried SlNo to Text (DATA Type)
As per below I getting the right result.

Please advise me how to get the result. With integer data type
In a form vb

Dim StrSql As String
Dim StrCri As String
Dim X As String

X = rst.Fields("Sr#").Value

StrCri = InputBox("Enter the Serial NO", "Enter number/", "Question/")
If Len(StrCri) = 0 Then
MsgBox Prompt:="No Criteria Specified", Title:="Search tblEmp.MDB"
Exit Sub
End If
StrSql = "SlNo='" & Trim(StrCri) & "'"

rst.FindFirst StrSql

If rst.NoMatch = False Then
Movefields
Else
MsgBox "Not Found."
rst.MoveFirst
End If

 

[Kiwiman]

Howzit


WELCOME TO THE FORUM BY THE WAY...


You can try the following...

Changed
strCri to lngCri and set it as datatype Long - the same as your Serial Number you are searching for.

When adding variables to a string expression you should follow the following rules

• Variable is a string - use apostrophe
  • strSQL = "SINo='" & yourVariable & "'"
• Variable is a Number - nothing required
  • strSQL = "SINo=" & yourVariable
• Variable is a Datetime field - use the # sysmbol
  • strSQL = "SINo=#" & yourVariable & "#"
  • You will also need to consider formatting to the US Date format
    • strSQL = "SINo=#" & format(yourVariable,"mm/dd/yy") & "#"

Dim StrSql As String
[B]Dim lngCri As Long[/B]
Dim X As String

X = rst.Fields("Sr#").Value

[B]lngCri[/B] = InputBox("Enter the Serial NO", "Enter number/", "Question/")
If Len([B]lngCri[/B]) = 0 Then
MsgBox Prompt:="No Criteria Specified", Title:="Search tblEmp.MDB"
Exit Sub
End If
StrSql = "SlNo=[B]" & lngCri [/B]

rst.FindFirst StrSql

If rst.NoMatch = False Then
Movefields
Else
MsgBox "Not Found."
rst.MoveFirst
End If

 

[sundaram]

Thanks for your kind reply, I appreciate your quick rensponse.
But dear it is not working it giving a error: " Runtime error '3077':, Syntax error (missing operator) in expression.

 

[Kiwiman]

Howzit

Where abouts do you get the error. If you put a stop after the frist line of the event procedure, you will be able to step throguh the code using the F8 key.

Could you aslo paste the entire event procedure code please.

 

[sundaram]

Thanks I got it.

I did the same thing but it didn't workout. After pasting your code, it workout. what mistake did I did Iam trying trece it.

Now I am trying to find the date as per your earlier reply I wrote the following code:
Dim Strsql As String
Dim DtCri As Date
Dim X As String
X = rst.Fields("Submittal date").Value

DtCri = InputBox("Enter Submittal Date", "Enter Date/", "Question/")
If Len(DtCri) = 0 Then
MsgBox prompt:="No Criteria Specified", Title:="Search tblemp.MDB"
Exit Sub
End If
Strsql = "submittal date=#" & DtCri
rst.FindFirst Strsql
If rst.NoMatch = False Then
Movefields
Else
MsgBox "Not Found."
rst.MoveFirst
End If

 

[sundaram]

Dear,

Thanks for your reply. The code what you have send I have pasted as per your instruction. Now it is working, but I have done the same. why it was giving me an error.

Please suggest me so that in future i can trace myself the problem.

Thanks once again.

 

[Kiwiman]

Howzit

Glad you got it working.

Some things to help you troubleshoot vba.

1. Use Stop - to halt the code then use F8 to step through one line at a time. That way you will know what line is causing the problem
2. To see what is held in a variable you can use the Debug.print syntax which will print in the immediate window the value assigned to a variable \ string
   • Debug.Print strSQL
3. I would not also have spaces in your field names
   • Replace Submittal Date with SubmitDate
   • Otherwise you will need to put a SQuare Bracket around the field - i do this by default anyway
4. Never use Reserved names for field names it may cause problems down the track. Avoid field names like Date, Month, Year, Name etc

Dim Strsql As String
Dim DtCri As Date
Dim X As String

stop ' halt the code and step through using the F8 key

X = rst.Fields("Submittal date").Value

DtCri = InputBox("Enter Submittal Date", "Enter Date/", "Question/")
If Len(DtCri) = 0 Then
MsgBox prompt:="No Criteria Specified", Title:="Search tblemp.MDB"
Exit Sub
End If
[COLOR="Blue"]Strsql = "[submittal date]=#" & DtCri [B]& "#"[/B][/COLOR] ' You missed the final qualifier
' Also beware of dates - I tend to format as the american date format
[COLOR="Red"]' Strsql = "[submittal date]=#" & format(DtCri,"mm/dd/yy") [B]&"#"[/B][/COLOR]

[B]debug.print strSQL[/B] ' Print in immediate window what has been assigned to the variable strSQL.  THis has no other effect on the operation of the code

rst.FindFirst Strsql
If rst.NoMatch = False Then
Movefields
Else
MsgBox "Not Found."
rst.MoveFirst
End If

 

[sundaram]

Howzit

Glad you got it working.

Some things to help you troubleshoot vba.

1. Use Stop - to halt the code then use F8 to step through one line at a time. That way you will know what line is causing the problem
2. To see what is held in a variable you can use the Debug.print syntax which will print in the immediate window the value assigned to a variable \ string
   • Debug.Print strSQL
3. I would not also have spaces in your field names
   • Replace Submittal Date with SubmitDate
   • Otherwise you will need to put a SQuare Bracket around the field - i do this by default anyway
4. Never use Reserved names for field names it may cause problems down the track. Avoid field names like Date, Month, Year, Name etc

Dim Strsql As String
Dim DtCri As Date
Dim X As String
 
stop ' halt the code and step through using the F8 key
 
X = rst.Fields("Submittal date").Value
 
DtCri = InputBox("Enter Submittal Date", "Enter Date/", "Question/")
If Len(DtCri) = 0 Then
MsgBox prompt:="No Criteria Specified", Title:="Search tblemp.MDB"
Exit Sub
End If
[COLOR=blue]Strsql = "[submittal date]=#" & DtCri [B]& "#"[/B][/COLOR] ' You missed the final qualifier
' Also beware of dates - I tend to format as the american date format
[COLOR=red]' Strsql = "[submittal date]=#" & format(DtCri,"mm/dd/yy") [B]&"#"[/B][/COLOR]
 
[B]debug.print strSQL[/B] ' Print in immediate window what has been assigned to the variable strSQL.  THis has no other effect on the operation of the code
 
rst.FindFirst Strsql
If rst.NoMatch = False Then
Movefields
Else
MsgBox "Not Found."
rst.MoveFirst
End If

Please check "form find date" thread i have send please.

 

[sundaram]

Thanks very much your coding example helped me a lot. You are excellent.

Howzit


WELCOME TO THE FORUM BY THE WAY...


You can try the following...

Changed
strCri to lngCri and set it as datatype Long - the same as your Serial Number you are searching for.


When adding variables to a string expression you should follow the following rules

• Variable is a string - use apostrophe
  • strSQL = "SINo='" & yourVariable & "'"
• Variable is a Number - nothing required
  • strSQL = "SINo=" & yourVariable
• Variable is a Datetime field - use the # sysmbol
  • strSQL = "SINo=#" & yourVariable & "#"
  • You will also need to consider formatting to the US Date format
    • strSQL = "SINo=#" & format(yourVariable,"mm/dd/yy") & "#"

Dim StrSql As String
[B]Dim lngCri As Long[/B]
Dim X As String
 
X = rst.Fields("Sr#").Value
 
[B]lngCri[/B] = InputBox("Enter the Serial NO", "Enter number/", "Question/")
If Len([B]lngCri[/B]) = 0 Then
MsgBox Prompt:="No Criteria Specified", Title:="Search tblEmp.MDB"
Exit Sub
End If
StrSql = "SlNo=[B]" & lngCri [/B]
 
rst.FindFirst StrSql
 
If rst.NoMatch = False Then
Movefields
Else
MsgBox "Not Found."
rst.MoveFirst
End If