• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Handling Queries in VBA Code

Hi, can anyone help me please?

I need some code that will enable me to modify two fields of certain multiple records in a table. The trigger will be from the yes button of a msgbox.

My first idea was to build a query to select the records, but then I don't know how to update the values in the query. Also, how can I dynamically send criteria to the query from the code?

Thanks in advance!
0
PJRimmer
Asked:
PJRimmer
1 Solution
 
dannywarehamCommented:
You need to use an UPDATE query.

If you can give examples of your table name, fields required and when the values would be changed, we can show you how to build the queryand incorporate into VBA
0
 
PJRimmerAuthor Commented:
I've done a bit more work on this and I am trying to do the following:

DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = True, tbl_VantageCharging.EndDate = Now()" & _
                "WHERE (tbl_VantageCharging.AccountNo = " & AccountNo & " AND tbl_VantageCharging.[Ended?] = False);"

Seems to be almost there, but it is complaining about a data type mismatch in the criteria. Any ideas? Am I on the right track?
0
 
PJRimmerAuthor Commented:
Sorry, missed a couple of lines. It's all part of a msgbox!

        If MsgBox("Do you want to end all current charges and start a new charge(s) for the new package?", vbYesNo + vbQuestion, "Change Charges?") = vbYes Then
            DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = True, tbl_VantageCharging.EndDate = Now()" & _
                "WHERE (tbl_VantageCharging.AccountNo = " & AccountNo & " AND tbl_VantageCharging.[Ended?] = False);"
        Else
        End If

Cheers!
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
dannywarehamCommented:
You've answered your own question!

As for teh data-type mismatch, this will be caused because the field "Ended?" or "EndDate" in table "tbl_VantageCharging" will only accept certain types of values (for example, a number field will not accept text).

Check teh type of data being added (change TRUE to -1 for example)
0
 
PJRimmerAuthor Commented:
Hey I was a bit hasty asking the question!

I have been messing around trying different things, but I sill get data type mismatch.

            DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = '-1', tbl_VantageCharging.EndDate = '" & Date & "' " & _
                "WHERE (tbl_VantageCharging.AccountNo = " & AccountNo & " AND tbl_VantageCharging.[Ended?] = '0');"

Any advice of use of brackets, '&' and quote marks?
0
 
HakimCommented:
my 2 cents!

There is no need of inclosing -1 and 0.
0
 
PJRimmerAuthor Commented:
Yeah I tried that but I still get the same error:

Run-time error '3464':

Data type mismatch in criteria expression.
0
 
shanesuebsahakarnCommented:
If any of the fields are text data types, you need to enclose the values that you are updating to with ' characters. If they are numerics, you don't, and dates use the # delimiter. In this example below, I'm assuming Ended is a numeric (boolean) field, EndDate is a Date/Time field and AccountNo is a text field:

            DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = -1, tbl_VantageCharging.EndDate = #" & Format(Date,"mm/dd/yyyy") & "# " & _
                "WHERE (tbl_VantageCharging.AccountNo = '" & AccountNo & "' AND tbl_VantageCharging.[Ended?] = 0);"
0
 
PJRimmerAuthor Commented:
Thanks, that all works fine now!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now