can you select the most recent date from a table in a query

[ForbesR]

I have 2 tables in Access, one holds Clients Initial Details, the other holds
a record of each time they are contacted, how can i pull the most recent
contact date for each Client? HELP PLEASE.

 

[Ofer]

Use Dmax in the query.
Create a NewField in the query and write

MaxDate: DMax("[DateFieldName]","[contactedTableName]","[CustNum] = " &
[CustNum])
=======================
In SQL it will look like
Select [CustNum], DMax("[DateFieldName]","[contactedTableName]","[CustNum] =
" & [CustNum]) As MaxDate From TableName

 

[Donna]

I have a database where, among other things, I need to calculate mileage. I
used your below example and substituted [EndingMileage] for [DateFieldName],
[Trip] for [contactedTableName] and [TagNumber] for [CustNum] which looks
like this:

MaxMileage: DMax("[EndingMileage]","[Trip]","[TagNumber] = " & [TagNumber])

Vehicle table name is [Vehicle] where [TagNumber] resides
Mileage/trip table name is [Trip] where [Ending mileage]s and [TagNumber]s
are stored

When I run the above query I get this error statement:
"Syntax error (missing operator) in query expression '[TagNumber] = A10-4741F'

What am I doing wrong and is there a template for calculating mileage?

Thanks.

--
Donna N.

Use Dmax in the query.
Create a NewField in the query and write

MaxDate: DMax("[DateFieldName]","[contactedTableName]","[CustNum] = " &
[CustNum])
=======================
In SQL it will look like
Select [CustNum], DMax("[DateFieldName]","[contactedTableName]","[CustNum] =
" & [CustNum]) As MaxDate From TableName

--
\\// Live Long and Prosper \\//
BS"D

I have 2 tables in Access, one holds Clients Initial Details, the other holds
a record of each time they are contacted, how can i pull the most recent
contact date for each Client? HELP PLEASE.

 

[vanderghast]

You are missing the delimiting double quotes:

MaxMileage: DMax("[EndingMileage]","[Trip]","[TagNumber] = """ & [TagNumber]
& """" )


It may be easier to figure it out from the error message, since the right
expression, once printed, should have been:

'[TagNumber] = "A10-4741F" '

instead of the actual

'[TagNumber] = A10-4741F'



See, the double quotes which EXPLICITLY 'limits' the string content, else,
it is read as variable A10 from which we subtract 4741f (whatever 4741f
could be) and the computer gave up, trying to understand. To add the
delimiter, a double quote, we need to DOUBLE it, since it is already inside
a string:

ThisIsStringWithAspaceAQuoteAndASpace= " "" "

ThisIsStringWithSomeTextAQuoteAndASpace= "SomeText "" "

or " [TagNumber]= "" "


So the proposed syntax:

" [TagNumber]= "" " & fieldName & " "" "


where you need to remove a couple of space, since you want something like:
"A10-4741F"

not
" A10-4741F "




Vanderghast, Access MVP

I have a database where, among other things, I need to calculate mileage.
I
used your below example and substituted [EndingMileage] for
[DateFieldName],
[Trip] for [contactedTableName] and [TagNumber] for [CustNum] which looks
like this:

MaxMileage: DMax("[EndingMileage]","[Trip]","[TagNumber] = " &
[TagNumber])

Vehicle table name is [Vehicle] where [TagNumber] resides
Mileage/trip table name is [Trip] where [Ending mileage]s and [TagNumber]s
are stored

When I run the above query I get this error statement:
"Syntax error (missing operator) in query expression '[TagNumber] =
A10-4741F'

What am I doing wrong and is there a template for calculating mileage?

Thanks.

--
Donna N.

Use Dmax in the query.
Create a NewField in the query and write

MaxDate: DMax("[DateFieldName]","[contactedTableName]","[CustNum] = " &
[CustNum])
=======================
In SQL it will look like
Select [CustNum],
DMax("[DateFieldName]","[contactedTableName]","[CustNum] =
" & [CustNum]) As MaxDate From TableName

--
\\// Live Long and Prosper \\//
BS"D

I have 2 tables in Access, one holds Clients Initial Details, the other
holds
a record of each time they are contacted, how can i pull the most
recent
contact date for each Client? HELP PLEASE.

 

[Donna]

Thank you. It works perfectly now. And I appreciate the explanation as it
will help with possible future issues.

On another issue, I'd like to be able to use that DMAX [EndingMilege] to
populate another field on a form(with a subform) [StartingMileage] and both
fields' data saved in the same table, i.e. the [EndingMileage] for vehicle
trip record#1 is added to the next vehicle trip record#2 as [StartingMileage]
so that the mileage on record#2 can be calculated after [EndingMileage] is
entered on record#2.

Is that possible or is there another way to be able to calculate mileage for
each trip without having the user enter both [StartingMileage] and
[EndingMileage] for each trip?

The only templates I've been able to find relating to a mileage log is for
Excel. It would be a breeze to do it with Excel, but would like to be able
to use my Access database.

Thanks again.
--
Donna N.

You are missing the delimiting double quotes:

MaxMileage: DMax("[EndingMileage]","[Trip]","[TagNumber] = """ & [TagNumber]
& """" )


It may be easier to figure it out from the error message, since the right
expression, once printed, should have been:

'[TagNumber] = "A10-4741F" '

instead of the actual

'[TagNumber] = A10-4741F'



See, the double quotes which EXPLICITLY 'limits' the string content, else,
it is read as variable A10 from which we subtract 4741f (whatever 4741f
could be) and the computer gave up, trying to understand. To add the
delimiter, a double quote, we need to DOUBLE it, since it is already inside
a string:

ThisIsStringWithAspaceAQuoteAndASpace= " "" "

ThisIsStringWithSomeTextAQuoteAndASpace= "SomeText "" "

or " [TagNumber]= "" "


So the proposed syntax:

" [TagNumber]= "" " & fieldName & " "" "


where you need to remove a couple of space, since you want something like:
"A10-4741F"

not
" A10-4741F "




Vanderghast, Access MVP

I have a database where, among other things, I need to calculate mileage.
I
used your below example and substituted [EndingMileage] for
[DateFieldName],
[Trip] for [contactedTableName] and [TagNumber] for [CustNum] which looks
like this:

MaxMileage: DMax("[EndingMileage]","[Trip]","[TagNumber] = " &
[TagNumber])

Vehicle table name is [Vehicle] where [TagNumber] resides
Mileage/trip table name is [Trip] where [Ending mileage]s and [TagNumber]s
are stored

When I run the above query I get this error statement:
"Syntax error (missing operator) in query expression '[TagNumber] =
A10-4741F'

What am I doing wrong and is there a template for calculating mileage?

Thanks.

--
Donna N.

Use Dmax in the query.
Create a NewField in the query and write

MaxDate: DMax("[DateFieldName]","[contactedTableName]","[CustNum] = " &
[CustNum])
=======================
In SQL it will look like
Select [CustNum],
DMax("[DateFieldName]","[contactedTableName]","[CustNum] =
" & [CustNum]) As MaxDate From TableName

--
\\// Live Long and Prosper \\//
BS"D


:

I have 2 tables in Access, one holds Clients Initial Details, the other
holds
a record of each time they are contacted, how can i pull the most
recent
contact date for each Client? HELP PLEASE.

 

[vanderghast]

It is possible to get the mileage between trips with only the
EndingMilleage. The trick is to bring the table TWICE.


SELECT a.carID, a.EndingMilleage, MAX(b.EndingMilleage) As StartingMilleage
FROM trips AS a LEFT JOIN trips AS b
ON a.carID=b.carID AND a.EndingMilleage> b.EndingMilleage
GROUP BY a.carID, a.EndingMilleage


should do, for a table, trips, with fields carID and EndingMilleage.
StartingMilleage is computed; note though, for the first record for each
car, there is no 'previous' record for that record, so there is no
"StartingMilleage" and here it shows as a NULL.



Vanderghast, Access MVP

Thank you. It works perfectly now. And I appreciate the explanation as
it
will help with possible future issues.

On another issue, I'd like to be able to use that DMAX [EndingMilege] to
populate another field on a form(with a subform) [StartingMileage] and
both
fields' data saved in the same table, i.e. the [EndingMileage] for vehicle
trip record#1 is added to the next vehicle trip record#2 as
[StartingMileage]
so that the mileage on record#2 can be calculated after [EndingMileage] is
entered on record#2.

Is that possible or is there another way to be able to calculate mileage
for
each trip without having the user enter both [StartingMileage] and
[EndingMileage] for each trip?

The only templates I've been able to find relating to a mileage log is for
Excel. It would be a breeze to do it with Excel, but would like to be
able
to use my Access database.

Thanks again.
--
Donna N.

You are missing the delimiting double quotes:

MaxMileage: DMax("[EndingMileage]","[Trip]","[TagNumber] = """ &
[TagNumber]
& """" )


It may be easier to figure it out from the error message, since the right
expression, once printed, should have been:

'[TagNumber] = "A10-4741F" '

instead of the actual

'[TagNumber] = A10-4741F'



See, the double quotes which EXPLICITLY 'limits' the string content,
else,
it is read as variable A10 from which we subtract 4741f (whatever
4741f
could be) and the computer gave up, trying to understand. To add the
delimiter, a double quote, we need to DOUBLE it, since it is already
inside
a string:

ThisIsStringWithAspaceAQuoteAndASpace= " "" "

ThisIsStringWithSomeTextAQuoteAndASpace= "SomeText "" "

or " [TagNumber]= "" "


So the proposed syntax:

" [TagNumber]= "" " & fieldName & " "" "


where you need to remove a couple of space, since you want something
like:
"A10-4741F"

not
" A10-4741F "




Vanderghast, Access MVP

I have a database where, among other things, I need to calculate
mileage.
I
used your below example and substituted [EndingMileage] for
[DateFieldName],
[Trip] for [contactedTableName] and [TagNumber] for [CustNum] which
looks
like this:

MaxMileage: DMax("[EndingMileage]","[Trip]","[TagNumber] = " &
[TagNumber])

Vehicle table name is [Vehicle] where [TagNumber] resides
Mileage/trip table name is [Trip] where [Ending mileage]s and
[TagNumber]s
are stored

When I run the above query I get this error statement:
"Syntax error (missing operator) in query expression '[TagNumber] =
A10-4741F'

What am I doing wrong and is there a template for calculating mileage?

Thanks.

--
Donna N.


:

Use Dmax in the query.
Create a NewField in the query and write

MaxDate: DMax("[DateFieldName]","[contactedTableName]","[CustNum] = "
&
[CustNum])
=======================
In SQL it will look like
Select [CustNum],
DMax("[DateFieldName]","[contactedTableName]","[CustNum] =
" & [CustNum]) As MaxDate From TableName

--
\\// Live Long and Prosper \\//
BS"D


:

I have 2 tables in Access, one holds Clients Initial Details, the
other
holds
a record of each time they are contacted, how can i pull the most
recent
contact date for each Client? HELP PLEASE.

 

[Donna]

Vanderghast,

This is way cool and worked perfectly! Exactly what I had been struggling
with for weeks traying to make happen.

I sincerely appreciate your assistance expertise.
--
Donna N.

It is possible to get the mileage between trips with only the
EndingMilleage. The trick is to bring the table TWICE.


SELECT a.carID, a.EndingMilleage, MAX(b.EndingMilleage) As StartingMilleage
FROM trips AS a LEFT JOIN trips AS b
ON a.carID=b.carID AND a.EndingMilleage> b.EndingMilleage
GROUP BY a.carID, a.EndingMilleage


should do, for a table, trips, with fields carID and EndingMilleage.
StartingMilleage is computed; note though, for the first record for each
car, there is no 'previous' record for that record, so there is no
"StartingMilleage" and here it shows as a NULL.



Vanderghast, Access MVP

Thank you. It works perfectly now. And I appreciate the explanation as
it
will help with possible future issues.

On another issue, I'd like to be able to use that DMAX [EndingMilege] to
populate another field on a form(with a subform) [StartingMileage] and
both
fields' data saved in the same table, i.e. the [EndingMileage] for vehicle
trip record#1 is added to the next vehicle trip record#2 as
[StartingMileage]
so that the mileage on record#2 can be calculated after [EndingMileage] is
entered on record#2.

Is that possible or is there another way to be able to calculate mileage
for
each trip without having the user enter both [StartingMileage] and
[EndingMileage] for each trip?

The only templates I've been able to find relating to a mileage log is for
Excel. It would be a breeze to do it with Excel, but would like to be
able
to use my Access database.

Thanks again.
--
Donna N.

You are missing the delimiting double quotes:

MaxMileage: DMax("[EndingMileage]","[Trip]","[TagNumber] = """ &
[TagNumber]
& """" )


It may be easier to figure it out from the error message, since the right
expression, once printed, should have been:

'[TagNumber] = "A10-4741F" '

instead of the actual

'[TagNumber] = A10-4741F'



See, the double quotes which EXPLICITLY 'limits' the string content,
else,
it is read as variable A10 from which we subtract 4741f (whatever
4741f
could be) and the computer gave up, trying to understand. To add the
delimiter, a double quote, we need to DOUBLE it, since it is already
inside
a string:

ThisIsStringWithAspaceAQuoteAndASpace= " "" "

ThisIsStringWithSomeTextAQuoteAndASpace= "SomeText "" "

or " [TagNumber]= "" "


So the proposed syntax:

" [TagNumber]= "" " & fieldName & " "" "


where you need to remove a couple of space, since you want something
like:
"A10-4741F"

not
" A10-4741F "




Vanderghast, Access MVP



I have a database where, among other things, I need to calculate
mileage.
I
used your below example and substituted [EndingMileage] for
[DateFieldName],
[Trip] for [contactedTableName] and [TagNumber] for [CustNum] which
looks
like this:

MaxMileage: DMax("[EndingMileage]","[Trip]","[TagNumber] = " &
[TagNumber])

Vehicle table name is [Vehicle] where [TagNumber] resides
Mileage/trip table name is [Trip] where [Ending mileage]s and
[TagNumber]s
are stored

When I run the above query I get this error statement:
"Syntax error (missing operator) in query expression '[TagNumber] =
A10-4741F'

What am I doing wrong and is there a template for calculating mileage?

Thanks.

--
Donna N.


:

Use Dmax in the query.
Create a NewField in the query and write

MaxDate: DMax("[DateFieldName]","[contactedTableName]","[CustNum] = "
&
[CustNum])
=======================
In SQL it will look like
Select [CustNum],
DMax("[DateFieldName]","[contactedTableName]","[CustNum] =
" & [CustNum]) As MaxDate From TableName

--
\\// Live Long and Prosper \\//
BS"D


:

I have 2 tables in Access, one holds Clients Initial Details, the
other
holds
a record of each time they are contacted, how can i pull the most
recent
contact date for each Client? HELP PLEASE.