• 0

[VBA] DSum and Variables


Question

I've got an issue with the DSum and a variable.

Basically I've got a database where courses are assigned to specific instructors. An instructor can not be assigned more than 18 lec units in a given period of time. In order to tally these lec units, I'm using the dsum function.

I can hardcode an instructors information into the program as follows:

sumTest = DSum("[Lec Unit]", "Assign_Query", "[LastName] = 'Blasius'")

post-3351-1259647441_thumb.jpg

That works great, if I only had one instructor.

I decided to introduce a variable where it pulls information from a textbox, in this case, the instructors last name.

I'm having a hard time getting the dsum to get along with the variable:

Dim lNameVar as String
...
sumTest = DSum("[Lec Unit]", "Assign_Query", "[LastName] = " & lNameVar &"")

I get the following error:

post-3351-1259647768.png

When I run it against Access:

post-3351-1259647695_thumb.png

I've messed around with the quotes, to no luck.

SumTest = DSum("[Lec Unit]", "Assign_Query", "[LastName] = " & lastName & ")

SumTest = DSum("[Lec Unit]", "Assign_Query", "[LastName] = " & lastName )

SumTest = DSum("[Lec Unit]", "Assign_Query", "[LastName] = " & lastName & )

etc..

Anyone able to shed some light on this problem? :)

Edited by Blasius
Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Yes, yuo are missing out the single quotes around teh lastname variable.

^ Apologies for typos!

SumTest = DSum("[Lec Unit]", "Assign_Query", "[LastName] = '" & lastName & "'")

^ Should solve it. (Copy/Paste)

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.