[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Handling Queries in VBA Code

Posted on 2004-09-15
9
Medium Priority
?
276 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
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.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

649 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