autonumber feild in criteria - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

autonumber feild in criteria

Author

Topic

kuku1115
Starting Member

5 Posts

Posted - 2004-12-14 : 10:07:28

Hi
I am having some problem with autonumber field .i have made a form in which I enter the criteria on which I want my search to be based on . we have to enter the value in the form & based on the value entered there I a query which will select value from the table. Now there are 3 fields .everthing is working fine with the 2 text fields but one of my field is an autonumber. Now here I have problem. I think that’s because the value of the select query is assigned to string.but I don’t know what changes would correct this problem as other 2 fields in criteria are text.i am using ms access

strSQL = "SELECT TicketNumber,store_id,problemdescription,priority,open_date,status " & _
"FROM tbl_transaction_problem " & _
"WHERE tbl_transaction_problem.TicketNumber" & intTicketNumber & _
"AND tbl_transaction_problem.Priority" & strPriority & _
"AND tbl_transaction_problem.Status" & strStatus & _
"ORDER BY tbl_transaction_problem.Priority;"

' Pass the SQL string to the query
qdf.SQL = strSQL
thanks

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-12-14 : 11:50:26

There are a few things that come to mind. First, the small details:

You appear to be missing the equals signs in all three parts of your WHERE clause. It should read something like "WHERE tbl_transaction_problem.TicketNumber = " & intTicketNumber...

Second, your WHERE clause is using ANDs. Do you really want to limit your result set to only the rows that meet ALL THREE criteria? From your description, I would have expected it to have ORs instead.

Third, when querying string values they should be enclosed in single quotation marks, so I would expect the second part of your where clause to look like "OR tbl_transaction_problem.Priority = '" & strPriority & "'" &_

Finally, is your form that you are using to enter these fields UNBOUND? I find a lot of people get in trouble in Access because they only create forms that are bound to tables. In your case if all you are doing is using the values to create this query, then your form should be unbound. Or at least these three fields should be unbound. The form does not need to know that the ticketnumber integer is going to match against an autonumber other than to make sure it is a long integer data type.

Hope that helps!

-----------------------------------------
Professional IT Solutions from Infoneering

kuku1115
Starting Member

5 Posts

Posted - 2004-12-14 : 12:23:38

hi
thanks for the reply.myform is working properly for priority and status field (which are text fields) without "=" so i don't think thats the problem & i haave specified and because in the first half i am taking values from the form & if user has not enterd any value than i specified like*.so thats not the problem

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-14 : 13:02:51

as Mark patiently potented out, the code you have posted does not work. it does not form proper SQL statments -- unless those "str" variables contain values like:

='somevalue'

if they DO, then perhaps it might be helpful if you told us this? we cannot help you unless you give us all the info ...

- Jeff

kuku1115
Starting Member

5 Posts

Posted - 2004-12-14 : 14:23:31

ok
i am sending the whole code

Option Compare Database
Option Explicit

Private Sub cmdOK_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strStatus As String
Dim strPriority As String
Dim intTicketNumber As Long
Dim strSQL As Variant
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qry_problem") Then
Set qdf = db.CreateQueryDef("qry_problem")
Else
Set qdf = db.QueryDefs("qry_problem")
End If
' Get the values from the combo boxes
If IsNull(Me.cboTicketNumber.Value) Then
intTicketNumber = " Like '*' "
Else
intTicketNumber = "='" & Me.cboTicketNumber.Value & "' "
End If
If IsNull(Me.cboPriority.Value) Then
strPriority = " Like '*' "
Else
strPriority = "='" & Me.cboPriority.Value & "' "
End If

If IsNull(Me.cboStatus.Value) Then
strStatus = " Like '*' "
Else
strStatus = "='" & Me.cboStatus.Value & "' "
End If
' Build the SQL string
strSQL = "SELECT TicketNumber,store_id,problemdescription,priority,open_date,status " & _
"FROM tbl_transaction_problem " & _
"WHERE tbl_transaction_problem.TicketNumber" & intTicketNumber & _
"AND tbl_transaction_problem.Priority" & strPriority & _
"AND tbl_transaction_problem.Status" & strStatus & _
"ORDER BY tbl_transaction_problem.Priority;"

' Pass the SQL string to the query
qdf.SQL = strSQL

' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qry_problem") = acObjStateOpen Then
DoCmd.Close acQuery, "qry_problem"
End If
' Open the query
DoCmd.OpenQuery "qry_problem"
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-12-14 : 14:31:45

Well, the problem with your approach is that you have defined intTicketNumber as a Long Integer and then later you assign it the value of "Like '*'" which most definitely is NOT and integer, long or otherwise.

So, either take a shortcut and redefine intTicketNumber as a String, or I would suggest modifying your building of the SQL statement to not even include that part of the WHERE clause if the value is null.

-----------------------------------------
Professional IT Solutions from Infoneering

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-14 : 15:03:19

if you are going to build WHERE clauses dynamically like this, instead of using LIKE "*" when a column is not part of the criteria, just ignore it -- don't append it to your WHERE clause.

And, as Mark points out, do not put quotes around numeric values, and do not use LIKE "*" on numerics.

also, keep in mind you can hard-code your SQL statement in your query to reference the values on this form, so you don't even need to do this. instead of building your query over and over (not good in a multi-user environment!) you can just hard-code your query (once!) like this:


SELECT 
  TicketNumber,store_id,problemdescription,priority,open_date,status
FROM 
  tbl_transaction_problem
WHERE 
  ((forms!YOurForm.cboTicketNumber is null) or (TicketNumber = forms!YOurForm.cboTicketNumber)) AND 
  ((forms!YourForm.cboPriority is null) or (Priority = forms!YourForm.cboPriority)) AND
  ((forms!YourForm.cboStatus is null) or (Status = forms!YourForm.cboStatus))

and get rid of ALL of your VB code and just open the report. the query will automatically refernce the values on your form as long as it is open.

(note: replace "YourForm", above, with your form name).

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources