?
Solved

Handling Queries in VBA Code

Posted on 2004-09-15
9
Medium Priority
?
270 Views
Last Modified: 2006-11-17
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
Comment
Question by:PJRimmer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 12063224
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
 

Author Comment

by:PJRimmer
ID: 12063247
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
 

Author Comment

by:PJRimmer
ID: 12063271
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 26

Expert Comment

by:dannywareham
ID: 12063274
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
 

Author Comment

by:PJRimmer
ID: 12063440
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
 
LVL 2

Expert Comment

by:Hakim
ID: 12063485
my 2 cents!

There is no need of inclosing -1 and 0.
0
 

Author Comment

by:PJRimmer
ID: 12063692
Yeah I tried that but I still get the same error:

Run-time error '3464':

Data type mismatch in criteria expression.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 1000 total points
ID: 12063732
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
 

Author Comment

by:PJRimmer
ID: 12064348
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question