DLookup in VB

[lala]

i'm trying to run an update query using VB and using DLookup as the value to update to

here's my code

    Dim SQL As String
    Dim varX As Variant
varX = DLookup("[sumofclaims]", "cl_total", "[group]=aetna")
    SQL = "UPDATE CLAetna Set CLAetna.TotalClaims = varx"

    DoCmd.RunSQL SQL

what am i doing wrong? i tried tying it to a button just to see if it works, and it doesn't


i have a table CL_TOTAL with 2 fields, group and sumofclaims
i need to update field TOTALCLAIMS in table CLAetna to the value from SUMOFCLAIMS in CL_TOTAL where GROUP=AETNA


so simple, why doesn't this work?

thank you

 

[georgedwilkinson]

Dim SQL As String
Dim varX As Variant
varX = DLookup("[sumofclaims]", "cl_total", "[group]=aetna")
SQL = "UPDATE CLAetna Set CLAetna.TotalClaims = " & varx
 
DoCmd.RunSQL SQL

or

Dim SQL As String
Dim varX As Variant
varX = DLookup("[sumofclaims]", "cl_total", "[group]=aetna")
SQL = "UPDATE CLAetna Set CLAetna.TotalClaims = '" & varx & "'"
 
DoCmd.RunSQL SQL

 

[lala]

THANK YOU!!!!!!!!!!!!!!!!!!!
you don't know what you've done for me

 

[stopher]

Dim SQL As String
Dim varX As Variant
varX = DLookup("[sumofclaims]", "cl_total", "[group]=aetna")
SQL = "UPDATE CLAetna Set CLAetna.TotalClaims = " & varx
 
DoCmd.RunSQL SQL

or

Dim SQL As String
Dim varX As Variant
varX = DLookup("[sumofclaims]", "cl_total", "[group]=aetna")
SQL = "UPDATE CLAetna Set CLAetna.TotalClaims = '" & varx & "'"
 
DoCmd.RunSQL SQL

Adding to George's answer where he is showing the solutions depending on whether varx is a string or a number, I think you also need to consider how you deal with aetna. So if it is a string then
varX = DLookup("[sumofclaims]", "cl_total", "[group]='aetna'")
note the single quotes.

Chris

 

[lala]

it is a string, but for some reason it worked the way i had it

i'm gonna rewrite these 10 times, for every insurance company that i have in that table
so you're saying that i should put them in single quotes?

 

[georgedwilkinson]

Yes, single quotes is the standard method.

 

[stopher]

it is a string, but for some reason it worked the way i had it

i'm gonna rewrite these 10 times, for every insurance company that i have in that table
so you're saying that i should put them in single quotes?

I'm surprised to hear you say that, but if it works, don't fix it

Regarding the 10 times to re-write, you could use a recordset to loop though your table and get all the insurance companies and run them ( to save you writing 10 statements and have the worry of adding more if you add more insurances). Can't explain now (maybe someone else can) as I'm half drunk and I'm note that great at coding

Chris

 

[lala]

never mind, you're totally right
they have to be in single quotes, without it it updates all tables to Aetna's number of claims
thank you too!!!!
you, guys, are amazing, really

 

[lala]

first of all, don't be surprised to hear me say anything )))))), i self taught access, and now i'm self learning vb
i designed a few pretty complex databases, but i mostly used macros and workarounds with queries
of course vb is much more powerful, so i'm trying to learn it

i'm not sure i can loop through it, each insurance company's number of claims has to go in that company's table

how will it know where to put it?

 

[georgedwilkinson]

That's not a good design so you're gonna need to handle them one at a time. No real value in looping with an un-normalized design.

 

[lala]

this is not a database that i'm designing
i know, i would never do something like that

i'm just automating some of my tasks
there're 10 reports that i have to run monthly for 10 ins companies, and instead of populating the data by hand, i'm automating it

 

[georgedwilkinson]

You're on the right track...you just gotta work 10 times as hard as you would if you had a normalized design. Not knocking it, I probably would never be able to get a job at my age if people did it right from the beginning. And I like having 10 times as many billable hours.

 

[lala]

i changed it, put them all together
how do you loop through? i already did this, but want to know for the future

so how would i loop to update all 10 clients?

 

[georgedwilkinson]

To loop through you need to create a DAO or ADO recordset and set up a program loop in VBA. There are tons of examples of this on the forums and I'm a bit pressed for time right now.

 

[lala]

that's fine, i will look them up, as long as they're here
and then, if i have questions, i will ask
thank you