Help with Expressions; Dsum and DLookUp.

[louisv91]

Hi everyone, I'm not particularly fluent with Access so do excuse amateur terminology, explanations etc.

Anyway; I've been setting up a Booking system through MS Access, to run alongside a PhP server. The Database includes information as you'd expect, Customer info, booking Info etc.

My problem lies in a Query where I want to include customer information (First name, surname) and booking information (Date, time and Price), I lastly want to include a 'Total' column which, as you'd expect, calculates the total price for each customer, additionally; I have set up a parameter under 'First Name' so the database can filter the results and show just the information relevant to them.

I have tried expressions such as:

Total: Dsum("Price","CurrentQuery","Customer Name =" & [Customer Name])

When the query is run I receive the "#Error" message.

So I took another approach and tried to look up the Total values from another query I quickly made, which was:

Total: DLookUp("TotalPrice","DifferentQuery","Customer Name =" & [Customer Name])

Again, I received the same error message.


Could anybody offer any solutions to my problem, perhaps an alternate approach?


Thanks in advance!

 

[plog]

In the criteria portion of your Dlookup and DSum functions you are comparing them as if the data is numerical, you need to treat them as strings. You have this:

"Customer Name =" & [Customer Name]

It should be this:

"Customer Name ='" & [Customer Name] & "'"


The above surrounds the variable name by single quote marks. If you were [Customer Name] was numerical, it would have worked with your initial code.

 

[louisv91]

Thanks for speedy response.

I attempted to enter the new code:

Total: DSum("Price","CurrentQuery","Customer Name ='" & [Customer Name] & "'") but I got:

Syntax error (Missing operator) in Query expression 'Customer Name ='John".

Appreciate the help, thanks again.

 

[Mihail]

Change the field name from Customer Name to CustomerName and will work.

 

[louisv91]

Just one error to the next!

Tried it and have received, for the first time, this error message:

"The expression you've entered as a query parameter produced this error: Microsoft Office Access can't find the name 'Enter Name' you've entered in the expression" (I definitely entered the correct names, btw)

It proceeds to run the query, but includes all the unfiltered results. :banghead:

Any recommendations? (Other than slamming my laptop against the wall)

thanks

 

[plog]

Putting brackets around the first Customer Name in the DSUm would do it too. Mihail is right though--the right way is to have table and field names only alphanumeric characters (no spaces).

 

[plog]

Can you post all of your SQL for this query?

 

[Mihail]

Be aware: the wall can crash.

So, you changed the field name. Hope you also changed the control name in the form.
So, one more try before go to bed (is bedtime in Romania):

Total: DSum("Price","CurrentQuery","CustomerName ='" Forms!YourFormName.CustomerName & "'")

Replace the blue part with your form name.

 

[louisv91]

Yeah I changed the field name to 'FirstName' in the customer table.

The SQL below again produced another syntax error.

Total: DSum("Price","CurrentQuery","CustomerName ='" Tables!Customer Table.CustomerName & "'") .


This is the only SQL I'm using for this Query, other than that it's all just data recall from various tables. The latest version (One that cannot match the criteria) is:

Total: DSum("Price","Forum","FirstName ='" & [Customer Name] & "'").

Appreciate the help guys.

 

[Mihail]

Save your DB as Access 2003 version (I use 2007)
Do a Compact and Repair
ZIP the DB.
Upload.

 

[louisv91]

Thanks a lot pal, I'll do it later when I get home.

 

[louisv91]

Sorry about the delay pal.

Because my database is quite unclean (Lots of test queries etc) I quickly made a small replica of the problem I've encountered.

Set up 2 tables with relationships; Customer/Booking both with some dummy data and a query which is identical to the one i'm currently trying to employ. I've entered the code:

Total: DSum("Price","Query","CustomerName ='" & [Customer Name] & "'") and am still receiving the same errors.

Thanks for taking a look, bud.

View attachment Booking.zip

 

[Mihail]

You can't because is not possible.

 

[louisv91]

Looks really nice, thanks a lot bud!