Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2013
    Posts
    81

    Question Unanswered: MS Access 03 - data mismatch/missing operator error with VBA update query

    I know this is a really silly question, but its been doing my head in for the last 2 days now and I would really appreciate a bit of advice.

    I've got a form which is based on a query (select query). The query joins 2 tables (tblWeight and tblResultWeight), and only displays results linked to a particular SetID from tbleResultWeight.

    The form is designed for data entry purposes, to allow users to enter test results for the equipment (in this case weights) under test. As the results table only stores the weight ID the join is used to also display other important information relating to the weight (i.e. its quantity, whether its missing/quarantined etc).

    I'm trying to build an update query behind the weight quarantined check box (attached to tblWeight). The idea is if the user clicks the quarantined button the system confirms they want to do this, prompts for input of certain data, updates the audit log and then checks the quarantine button attached to tblResultWeight. This may seem a little duplicitous, but I need to be able to see that a weight was quarantined when looking at historical test data, if I stick purely with the link to tblWeight, the quarantine information will be current not historical. My problem occurs with the update to the quarantine check box in tblResultWeight. I keep getting errors on the criteria.

    The code is (chopped out irrelevant bits):

    Code:
    Private Sub WeightQuarantined_Click()
    
    Dim testNo As Long
    Dim StrUpdateSQL As String
    
    .... other code in here to prompt user input and update audit log
    
                        testNo = Me.ResultWeightTestNumber
                        Debug.Print testNo
                        
                        'run UPDATE query to check quarantine box in WeightResults table
                        StrUpdateSQL = "UPDATE tblResultWeight " & _
                                       "SET tblResultWeight.[ResultWeightQuarantined] = True " & _
                                       "WHERE tblResultWeight.[ResultWeightTestNumber] = ' & testNo & ' "
                        Debug.Print StrUpdateSQL
                        CurrentDb.Execute StrUpdateSQL
                        Exit Sub
                    
    End Sub
    With this bit of code the error I get is "run-time error 3464, data type mismatch in criteria expression. I'm fairly certain my issue is with the testNo variable in the WHERE statement. I've tried several different versions of stating testNo with the result of either a data mismatch error or a missing operator (runtime error 3075) error. The code works if I hardcode testNo as 2 (I mean replace '&testNo&' with 2 in the WHERE clause), which supports the theory that my syntax is wrong. I'd be grateful if someone could advise the correct syntax for coding the testNo. testNo links to an autonumber field by the way.

    I had found some time ago a great syntax guide, but I can't find it anywhere now. Unfortunately most of the guides I've found recently don't seem to cover number variables (either that or I'm blind and daft - a distince possibility admittedly lol). If anyone knows of any good guides I'd be grateful if you could let me know.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. It's the value of the variable testNo that you want to search for, not the litteral "testNo", so:
    Code:
     "WHERE tblResultWeight.[ResultWeightTestNumber] = '" & testNo & "'"
    2.If the column ResultWeightTestNumber is defined as numeric in the table tblResultWeight, you musn't use the single quotes around the value:
    Code:
    "WHERE tblResultWeight.[ResultWeightTestNumber] = " & testNo
    Note: Since the names of the columns are exclusively alphanumeric, you do not need to use the square brackets either:
    Code:
                        StrUpdateSQL = "UPDATE tblResultWeight " & _
                                       "SET tblResultWeight.ResultWeightQuarantined = True " & _
                                       "WHERE tblResultWeight.ResultWeightTestNumber = " & testNo
    Last edited by Sinndho; 02-05-14 at 07:34.
    Have a nice day!

  3. #3
    Join Date
    Jun 2013
    Posts
    81
    Fantastic thanks Sinndho, your a star. It works now. Now for the next challenge lol.

    I think I know what I did now. I knew numerics didn't need quotes or hashes, and had tried using & testNo, but I think I probably stuck my end quotes (") after testNo rather than before. Such a silly little thing really, but then I suppose its easy when you know how.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    always always examine the actual sql being sent tot he sql engine..... looking at the VBA that creatres the SQL is OK, experienced developers may get 'there' sooner than inexperienced. but if you see the actual SQL its a heck of a lot easier to spot errors.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  6. #6
    Join Date
    Jun 2013
    Posts
    81
    Hi healdem, thanks for the comment. I know you've mentioned examining the SQL before and I've been looking into debugging techniques (think I'm using most of the ones I know about), but I'm not sure how to get to the actual SQL from the code. I've set up the code so that it prints strUpdate SQL (or whatever I called the string for the SQL code) into the immediate window (I have asked it to print it to a MsgBox before, but prefer the immediate window). Is this what you mean or is there more to it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •