DSUM for Dummies. Like Me! Some one please help

  • Thread starter Thread starter TheNovice
  • Start date Start date
T

TheNovice

OK,

I have done this before and know it is killing me that I cannot get it Right.

I want to do a DSUM on a particular Column.

Alive, Double
Date, "Short Date"
Passenger, YesNo

My form has the following fields,

dlyTpDate
dlyLocation

This is the formula that is in my field,

=DSum("[dead]","tblJBDataCargoAir"," [Location]=" & "'" & [dlyTPLocation] &
"' and [Date] =#" & [dlyDate] & "#")

I want something like this!!!

=DSum("[dead]","tblJBDataCargoAir","[passenger] = " True and "[Location]=" &
"'" & [dlyTPLocation] & "' and [Date] =#" & [dlyDate] & "#")

Now I want to add the yes No and all i keep getting is an error! What am I
doing wrong??????
 
Now I want to add the yes No and all i keep getting is an error! What am I
doing wrong??????
Click to expand...

Getting the quotes right can be really tricky. The key to it is that the third
argument needs to be a text string which is a valid WHERE clause. Try creating
a query in the query grid which gets the results that you want; select View...
SQL from the menu, and see how Access has constructed the WHERE clause in the
query. That will work if you build that same string in your domain function.

To build it, you need to concatenate literal text strings within quotes, with
variables from your VBA code. You need to include # marks as delimiters for
date field criteria, and either ' or " marks (within the quoted strings) as
delimiters for text fields. Use ' if possible, since it's easier to put '
within a string delimited by "; in order to include a " character you need two
consecutive " characters.

In your example try

=DSum("[dead]","tblJBDataCargoAir","[passenger] = True and [Location]='" &
[dlyTPLocation] & "' and [Date] =#" & [dlyDate] & "#")

This will concatenate the literal strings

[passenger] = true and [Location]='
Charles de Gaulle Airport
' and [Date] = #
5/6/2008
#

to give a final result

[passenger] = true and [Location]='Charles de Gaulle Airport' and [Date] =
#5/6/2008#

If there are Locations which might contain an apostrophe then you should not
use ' as a delimiter; you'll need a double doublequote instead:

=DSum("[dead]","tblJBDataCargoAir","[passenger] = True and [Location]=""" &
[dlyTPLocation] & """ and [Date] =#" & [dlyDate] & "#")

Spacing it out for readability, that would have

....[Location] = " " " & [dlyTPLocation] & " " " and...

The first two quotes after Location get translated to one doublequote; the two
quotes before the word and likewise. The result would be

[passenger] = true and [Location]="D'artegan Aerodrome" and [Date] =
#5/6/2008#
 
John,

Thanks for the quick response. I have pretty much been able to have it work
without the "Passenger = True". I get the sum from it and it look good, but
as soon as I put the "Passenger = true" statements is gives me a null entry
and I know there is something there.

Your thoughts?


--
-The Novice
Learn Today, Teach Tomorrow

Great Success is ones ability to ask for Help.


John W. Vinson said:
Now I want to add the yes No and all i keep getting is an error! What am I
doing wrong??????
Click to expand...

Getting the quotes right can be really tricky. The key to it is that the third
argument needs to be a text string which is a valid WHERE clause. Try creating
a query in the query grid which gets the results that you want; select View...
SQL from the menu, and see how Access has constructed the WHERE clause in the
query. That will work if you build that same string in your domain function.

To build it, you need to concatenate literal text strings within quotes, with
variables from your VBA code. You need to include # marks as delimiters for
date field criteria, and either ' or " marks (within the quoted strings) as
delimiters for text fields. Use ' if possible, since it's easier to put '
within a string delimited by "; in order to include a " character you need two
consecutive " characters.

In your example try

=DSum("[dead]","tblJBDataCargoAir","[passenger] = True and [Location]='" &
[dlyTPLocation] & "' and [Date] =#" & [dlyDate] & "#")

This will concatenate the literal strings

[passenger] = true and [Location]='
Charles de Gaulle Airport
' and [Date] = #
5/6/2008
#

to give a final result

[passenger] = true and [Location]='Charles de Gaulle Airport' and [Date] =
#5/6/2008#

If there are Locations which might contain an apostrophe then you should not
use ' as a delimiter; you'll need a double doublequote instead:

=DSum("[dead]","tblJBDataCargoAir","[passenger] = True and [Location]=""" &
[dlyTPLocation] & """ and [Date] =#" & [dlyDate] & "#")

Spacing it out for readability, that would have

....[Location] = " " " & [dlyTPLocation] & " " " and...

The first two quotes after Location get translated to one doublequote; the two
quotes before the word and likewise. The result would be

[passenger] = true and [Location]="D'artegan Aerodrome" and [Date] =
#5/6/2008#
Click to expand...
 
John,

PLEASE Disregard my last response. after re-reading, I took baby Steps as
you showed below and it worked, YOU DA MAN!

The Double Quotes is what I was missing and as soon as I did it it popped up
with the correct number....

Thanks a MILLION!
--
-The Novice
Learn Today, Teach Tomorrow

Great Success is ones ability to ask for Help.


John W. Vinson said:
Now I want to add the yes No and all i keep getting is an error! What am I
doing wrong??????
Click to expand...

Getting the quotes right can be really tricky. The key to it is that the third
argument needs to be a text string which is a valid WHERE clause. Try creating
a query in the query grid which gets the results that you want; select View...
SQL from the menu, and see how Access has constructed the WHERE clause in the
query. That will work if you build that same string in your domain function.

To build it, you need to concatenate literal text strings within quotes, with
variables from your VBA code. You need to include # marks as delimiters for
date field criteria, and either ' or " marks (within the quoted strings) as
delimiters for text fields. Use ' if possible, since it's easier to put '
within a string delimited by "; in order to include a " character you need two
consecutive " characters.

In your example try

=DSum("[dead]","tblJBDataCargoAir","[passenger] = True and [Location]='" &
[dlyTPLocation] & "' and [Date] =#" & [dlyDate] & "#")

This will concatenate the literal strings

[passenger] = true and [Location]='
Charles de Gaulle Airport
' and [Date] = #
5/6/2008
#

to give a final result

[passenger] = true and [Location]='Charles de Gaulle Airport' and [Date] =
#5/6/2008#

If there are Locations which might contain an apostrophe then you should not
use ' as a delimiter; you'll need a double doublequote instead:

=DSum("[dead]","tblJBDataCargoAir","[passenger] = True and [Location]=""" &
[dlyTPLocation] & """ and [Date] =#" & [dlyDate] & "#")

Spacing it out for readability, that would have

....[Location] = " " " & [dlyTPLocation] & " " " and...

The first two quotes after Location get translated to one doublequote; the two
quotes before the word and likewise. The result would be

[passenger] = true and [Location]="D'artegan Aerodrome" and [Date] =
#5/6/2008#
Click to expand...
 
Back
Top