dsum based on criteria

[paulevans]

HI I am having a real mental block with this problem so can any one help.

I am creating a driver database to keep track of approved drivers. I record any driving offence in a table called convictions. This is to track such items as speeding drink driving etc. What I want to do is look in the convictions table and see how many penalty points any one driver has.

I was going to use the following

dim pen as double

pen = dsum("Penpoints","Convictions","[name] = "& "'" & Me![named] & "'" And (date - [dateofofence] < 1860))

but this comes up with a type miss match error.
The date expression is to take the current date and deduct the date the person was fined on and if it is less than a given number of daya adds all the points together.

can any one see what I am doing wrong?
Thanks
Paul

 

[pbaldy]

...& "'" And..

Looking at that, do you think the And... will be within the quotes, as required?

 

[paulevans]

well

Hi Paul Thanks for response.
I still have not got my head around the use of quote mark in access I got the code to work with the following.

pens = DSum("[penpoints]", "[convictions]", "[driver] = 'paul evans' And [dateoffence] > Date()- 1280 ")

however I need to change 'paul evans' to me![name] what do I do with these quotations.

I have a similar problem with a findfirst string I would like to be able to look for the following.

[driver1] = me![name] or [driver2] = me![name]

thanks

Paul

 

[pbaldy]

You're on the right track getting it to work with hard coded values. Now you just need to replace Paul Evans with the form reference. To do that, close off the double quotes after the single quote, add an ampersand, replace the text with the form reference, then another ampersand and restart the double quotes. Basically you have to combine literal strings within quotes with form references that will be evaluated for their values outside the quotes.

I know it would be easier for both of us if I just did it, but if you puzzle it out now, you'll have it forever.

 

[paulevans]

Well Paul
thanks for that I am still not 100% about this but from what you said I came up with this:

pen = DSum("Penpoints", "Convictions", "[driver] = " & "'" & Me![Name] & "'" & " And [dateoffence] > Date() - 1860 ")

It seems to work. I will try the following for my other dilema.

str2 = "[driver1] = " & "'" & me![name] & "'" & "or [driver2] = " & "'" & me![name] & "'"

Thanks for help
Paul

 

[pbaldy]

This is just a personal preference, but I prefer to include the single quotes inside the doubles, rather than have them separate. It makes it more readable for me:

yours: pen = DSum("Penpoints", "Convictions", "[driver] = " & "'" & Me![Name] & "'" & " And [dateoffence] > Date() - 1860 ")
mine : pen = DSum("Penpoints", "Convictions", "[driver] = '" & Me![Name] & "' And [dateoffence] > Date() - 1860 ")

 

[paulevans]

Paul

Thanks again for all your help. I will give your way ago. The way I did it came from a previous forum and is a habbit that I will gladly forget.

Paul

 

[pbaldy]

They will both evaluate to the same thing, so it's mainly just a readability issue. Do it whichever way makes it easier for you to read. I was just throwing the option out there.