Second arguement to DLast

[Paul B.]

Is it possible to have two arguement to the DLast method?

I need to modify:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber])

to include where

'Disposition' = "Available"



Thanks in advance

 

[Paul B.]

Sorry about the double post, the previous post reported an error and I didn't
think it went through.

 

[Roger Carlson]

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber] & _
" And [Disposition] = 'Available'")

But this does not make sense to me. If you are trying to return the value
of Dispostion, but you are including a value for it in the criteria, the
only value it will ever return is "Available".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Paul B.]

Thanks Roger

What I need to do is list vehicles that are 'available' . My dispositions
table holds all the dispositions for all vehicles. What I am now wondering
is if I should be inserting the last disposition into the
'CurrentDisposition' in the tblVehicles instead. Thar way there will only be
one record with the current disposition for each vehicle.

I am open to suggestions.

Cheers

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber] & _
" And [Disposition] = 'Available'")

But this does not make sense to me. If you are trying to return the value
of Dispostion, but you are including a value for it in the criteria, the
only value it will ever return is "Available".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Is it possible to have two arguement to the DLast method?

I need to modify:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber])

to include where

'Disposition' = "Available"



Thanks in advance

 

[Roger Carlson]

Well, I'd need a lot more information than you've given. Table structure,
field names, how you're using this and so on.

However, if you want to find the most current value, you can use an
aggregate query (totals query). You would use the Max function (NOT the
Last function) on a date field in the record. But I have to warn you, it's
not easy to return other fields from the record. For an illustration of
this, go to my website (www.rogersaccesslibrary.com) and look for a sample
called "MaxQueryProblem.mdb". It will show the problem and two solutions
for it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks Roger

What I need to do is list vehicles that are 'available' . My dispositions
table holds all the dispositions for all vehicles. What I am now wondering
is if I should be inserting the last disposition into the
'CurrentDisposition' in the tblVehicles instead. Thar way there will only be
one record with the current disposition for each vehicle.

I am open to suggestions.

Cheers

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber] & _
" And [Disposition] = 'Available'")

But this does not make sense to me. If you are trying to return the value
of Dispostion, but you are including a value for it in the criteria, the
only value it will ever return is "Available".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Is it possible to have two arguement to the DLast method?

I need to modify:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber])

to include where

'Disposition' = "Available"



Thanks in advance

 

[Paul B.]

Ok then...here goes....



In the table 'tblVehicles' I have the following fields

VehicleID, VehicleNumber, SeviceDueDate, ServiceDueKms, CurrentDisposition,
CurrentLocation and VehicleDescription

In the table 'tblDispositions' I have the following fields

DispositionNumber, AuthorizedBy, DispositionDate, Disposition,
DispositionReason, DispositionNotes, VehicleLocation, VehicleNumber

A form 'frmEnterDisposition' allows the user to add a new disposition.

From the form 'frmEnterDisposition' I want to take the value of the field
'Disposition' and enter this value into the 'CurrentDisposition' field of the
table 'tblVehicles' and specifically into the row that matches the
VehicleNumber from the field "VehicleNumber'

I hope this answers your question. If not, could you recommend some other
way to do this?

Cheers

Well, I'd need a lot more information than you've given. Table structure,
field names, how you're using this and so on.

However, if you want to find the most current value, you can use an
aggregate query (totals query). You would use the Max function (NOT the
Last function) on a date field in the record. But I have to warn you, it's
not easy to return other fields from the record. For an illustration of
this, go to my website (www.rogersaccesslibrary.com) and look for a sample
called "MaxQueryProblem.mdb". It will show the problem and two solutions
for it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks Roger

What I need to do is list vehicles that are 'available' . My dispositions
table holds all the dispositions for all vehicles. What I am now wondering
is if I should be inserting the last disposition into the
'CurrentDisposition' in the tblVehicles instead. Thar way there will only be
one record with the current disposition for each vehicle.

I am open to suggestions.

Cheers

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber] & _
" And [Disposition] = 'Available'")

But this does not make sense to me. If you are trying to return the value
of Dispostion, but you are including a value for it in the criteria, the
only value it will ever return is "Available".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Is it possible to have two arguement to the DLast method?

I need to modify:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber])

to include where

'Disposition' = "Available"



Thanks in advance

 

[Roger Carlson]

Excellent description of the problem. I'm not a big fan of storing
"Current" values. Any number of things (program error, human error, etc)
can cause their values to be wrong. A better way is to simply query the
value when you want it. How exactly, depends on how you are using it.

For instance, say I had a form for the Vehicles. If I wanted to display the
current disposition, I could use a Domain Aggregate function as the
ControlSource for a textbox. Something like:
=DMax("Disposition", "tblDispostion","[VehicleNumber]=" &_
[Forms]![frmVehicle]![VehicleNumber])

This will not put the value in the tblVehicles table, but will display the
most current value on the form. BTW, notice that I used DMax rather than
DLast? Unless the domain (table or query) is sorted, the DLast will give
you a pretty random value. DMax is best.

Does this help any?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Ok then...here goes....



In the table 'tblVehicles' I have the following fields

VehicleID, VehicleNumber, SeviceDueDate, ServiceDueKms, CurrentDisposition,
CurrentLocation and VehicleDescription

In the table 'tblDispositions' I have the following fields

DispositionNumber, AuthorizedBy, DispositionDate, Disposition,
DispositionReason, DispositionNotes, VehicleLocation, VehicleNumber

A form 'frmEnterDisposition' allows the user to add a new disposition.

From the form 'frmEnterDisposition' I want to take the value of the field
'Disposition' and enter this value into the 'CurrentDisposition' field of the
table 'tblVehicles' and specifically into the row that matches the
VehicleNumber from the field "VehicleNumber'

I hope this answers your question. If not, could you recommend some other
way to do this?

Cheers

Well, I'd need a lot more information than you've given. Table structure,
field names, how you're using this and so on.

However, if you want to find the most current value, you can use an
aggregate query (totals query). You would use the Max function (NOT the
Last function) on a date field in the record. But I have to warn you, it's
not easy to return other fields from the record. For an illustration of
this, go to my website (www.rogersaccesslibrary.com) and look for a sample
called "MaxQueryProblem.mdb". It will show the problem and two solutions
for it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks Roger

What I need to do is list vehicles that are 'available' . My dispositions
table holds all the dispositions for all vehicles. What I am now wondering
is if I should be inserting the last disposition into the
'CurrentDisposition' in the tblVehicles instead. Thar way there will

only
be

one record with the current disposition for each vehicle.

I am open to suggestions.

Cheers



:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber] & _
" And [Disposition] = 'Available'")

But this does not make sense to me. If you are trying to return the value
of Dispostion, but you are including a value for it in the criteria, the
only value it will ever return is "Available".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Is it possible to have two arguement to the DLast method?

I need to modify:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber])

to include where

'Disposition' = "Available"



Thanks in advance

 

[Paul B.]

Ok, so this helps me a bit....

Perhaps you could help me understand how to achieve the next step then. If I
can't put the 'Disposition' into the tblVehicle.CurrentDisposition how would
I create a simple search for Vehicles that are 'Available'?

Remember that the tblDispositions will hold many 'Available' dispositions
for the same VehicleNumber. So, if I were to pull the latest disposition for
each vehicle, I would then need the form to filter out all but 'Availalble'.

Cheers

Excellent description of the problem. I'm not a big fan of storing
"Current" values. Any number of things (program error, human error, etc)
can cause their values to be wrong. A better way is to simply query the
value when you want it. How exactly, depends on how you are using it.

For instance, say I had a form for the Vehicles. If I wanted to display the
current disposition, I could use a Domain Aggregate function as the
ControlSource for a textbox. Something like:
=DMax("Disposition", "tblDispostion","[VehicleNumber]=" &_
[Forms]![frmVehicle]![VehicleNumber])

This will not put the value in the tblVehicles table, but will display the
most current value on the form. BTW, notice that I used DMax rather than
DLast? Unless the domain (table or query) is sorted, the DLast will give
you a pretty random value. DMax is best.

Does this help any?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Ok then...here goes....



In the table 'tblVehicles' I have the following fields

VehicleID, VehicleNumber, SeviceDueDate, ServiceDueKms, CurrentDisposition,
CurrentLocation and VehicleDescription

In the table 'tblDispositions' I have the following fields

DispositionNumber, AuthorizedBy, DispositionDate, Disposition,
DispositionReason, DispositionNotes, VehicleLocation, VehicleNumber

A form 'frmEnterDisposition' allows the user to add a new disposition.

From the form 'frmEnterDisposition' I want to take the value of the field
'Disposition' and enter this value into the 'CurrentDisposition' field of the
table 'tblVehicles' and specifically into the row that matches the
VehicleNumber from the field "VehicleNumber'

I hope this answers your question. If not, could you recommend some other
way to do this?

Cheers

Well, I'd need a lot more information than you've given. Table structure,
field names, how you're using this and so on.

However, if you want to find the most current value, you can use an
aggregate query (totals query). You would use the Max function (NOT the
Last function) on a date field in the record. But I have to warn you, it's
not easy to return other fields from the record. For an illustration of
this, go to my website (www.rogersaccesslibrary.com) and look for a sample
called "MaxQueryProblem.mdb". It will show the problem and two solutions
for it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks Roger

What I need to do is list vehicles that are 'available' . My dispositions
table holds all the dispositions for all vehicles. What I am now
wondering
is if I should be inserting the last disposition into the
'CurrentDisposition' in the tblVehicles instead. Thar way there will only
be
one record with the current disposition for each vehicle.

I am open to suggestions.

Cheers



:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber] & _
" And [Disposition] = 'Available'")

But this does not make sense to me. If you are trying to return the
value
of Dispostion, but you are including a value for it in the criteria, the
only value it will ever return is "Available".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Is it possible to have two arguement to the DLast method?

I need to modify:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber])

to include where

'Disposition' = "Available"



Thanks in advance

 

[Roger Carlson]

Sorry for the delay in responding. I was unavailable on Thursday and
Friday.

First of all, I did not say you "can't" store the current disposition, I
said I was not a big fan of it. If anything goes wrong with your process,
you could be storing incorrect information. There ARE times when storing it
is the most effective solution.

However, to create a query of vehicles that are currently available, you
would do something like this:

SELECT VehicleID, VehicleNumber, SeviceDueDate, ServiceDueKms,
DMax("Disposition", "tblDispostion","[VehicleNumber]=" [VehicleNumber]) AS
CurrentDisposition, CurrentLocation and VehicleDescription
FROM tblVehicles
WHERE CurrentDispostion = 'Available'

WARNING: I wrote the above of the top of my head and did not get a chance
to test it.


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Ok, so this helps me a bit....

Perhaps you could help me understand how to achieve the next step then. If I
can't put the 'Disposition' into the tblVehicle.CurrentDisposition how would
I create a simple search for Vehicles that are 'Available'?

Remember that the tblDispositions will hold many 'Available' dispositions
for the same VehicleNumber. So, if I were to pull the latest disposition for
each vehicle, I would then need the form to filter out all but 'Availalble'.

Cheers

Excellent description of the problem. I'm not a big fan of storing
"Current" values. Any number of things (program error, human error, etc)
can cause their values to be wrong. A better way is to simply query the
value when you want it. How exactly, depends on how you are using it.

For instance, say I had a form for the Vehicles. If I wanted to display the
current disposition, I could use a Domain Aggregate function as the
ControlSource for a textbox. Something like:
=DMax("Disposition", "tblDispostion","[VehicleNumber]=" &_
[Forms]![frmVehicle]![VehicleNumber])

This will not put the value in the tblVehicles table, but will display the
most current value on the form. BTW, notice that I used DMax rather than
DLast? Unless the domain (table or query) is sorted, the DLast will give
you a pretty random value. DMax is best.

Does this help any?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Ok then...here goes....



In the table 'tblVehicles' I have the following fields

VehicleID, VehicleNumber, SeviceDueDate, ServiceDueKms, CurrentDisposition,
CurrentLocation and VehicleDescription

In the table 'tblDispositions' I have the following fields

DispositionNumber, AuthorizedBy, DispositionDate, Disposition,
DispositionReason, DispositionNotes, VehicleLocation, VehicleNumber

A form 'frmEnterDisposition' allows the user to add a new disposition.

From the form 'frmEnterDisposition' I want to take the value of the field
'Disposition' and enter this value into the 'CurrentDisposition' field

of
the

table 'tblVehicles' and specifically into the row that matches the
VehicleNumber from the field "VehicleNumber'

I hope this answers your question. If not, could you recommend some other
way to do this?

Cheers


:

Well, I'd need a lot more information than you've given. Table structure,
field names, how you're using this and so on.

However, if you want to find the most current value, you can use an
aggregate query (totals query). You would use the Max function (NOT the
Last function) on a date field in the record. But I have to warn

you,
it's

not easy to return other fields from the record. For an illustration of
this, go to my website (www.rogersaccesslibrary.com) and look for a sample
called "MaxQueryProblem.mdb". It will show the problem and two solutions
for it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks Roger

What I need to do is list vehicles that are 'available' . My dispositions
table holds all the dispositions for all vehicles. What I am now
wondering
is if I should be inserting the last disposition into the
'CurrentDisposition' in the tblVehicles instead. Thar way there

will
only

be
one record with the current disposition for each vehicle.

I am open to suggestions.

Cheers



:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber] & _
" And [Disposition] = 'Available'")

But this does not make sense to me. If you are trying to return the
value
of Dispostion, but you are including a value for it in the

criteria,
the

only value it will ever return is "Available".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Is it possible to have two arguement to the DLast method?

I need to modify:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber])

to include where

'Disposition' = "Available"



Thanks in advance

 

[Paul B.]

Thanks for all your help Roger. Work's great! Cheers

Sorry for the delay in responding. I was unavailable on Thursday and
Friday.

First of all, I did not say you "can't" store the current disposition, I
said I was not a big fan of it. If anything goes wrong with your process,
you could be storing incorrect information. There ARE times when storing it
is the most effective solution.

However, to create a query of vehicles that are currently available, you
would do something like this:

SELECT VehicleID, VehicleNumber, SeviceDueDate, ServiceDueKms,
DMax("Disposition", "tblDispostion","[VehicleNumber]=" [VehicleNumber]) AS
CurrentDisposition, CurrentLocation and VehicleDescription
FROM tblVehicles
WHERE CurrentDispostion = 'Available'

WARNING: I wrote the above of the top of my head and did not get a chance
to test it.


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Ok, so this helps me a bit....

Perhaps you could help me understand how to achieve the next step then. If I
can't put the 'Disposition' into the tblVehicle.CurrentDisposition how would
I create a simple search for Vehicles that are 'Available'?

Remember that the tblDispositions will hold many 'Available' dispositions
for the same VehicleNumber. So, if I were to pull the latest disposition for
each vehicle, I would then need the form to filter out all but 'Availalble'.

Cheers

Excellent description of the problem. I'm not a big fan of storing
"Current" values. Any number of things (program error, human error, etc)
can cause their values to be wrong. A better way is to simply query the
value when you want it. How exactly, depends on how you are using it.

For instance, say I had a form for the Vehicles. If I wanted to display the
current disposition, I could use a Domain Aggregate function as the
ControlSource for a textbox. Something like:
=DMax("Disposition", "tblDispostion","[VehicleNumber]=" &_
[Forms]![frmVehicle]![VehicleNumber])

This will not put the value in the tblVehicles table, but will display the
most current value on the form. BTW, notice that I used DMax rather than
DLast? Unless the domain (table or query) is sorted, the DLast will give
you a pretty random value. DMax is best.

Does this help any?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Ok then...here goes....



In the table 'tblVehicles' I have the following fields

VehicleID, VehicleNumber, SeviceDueDate, ServiceDueKms,
CurrentDisposition,
CurrentLocation and VehicleDescription

In the table 'tblDispositions' I have the following fields

DispositionNumber, AuthorizedBy, DispositionDate, Disposition,
DispositionReason, DispositionNotes, VehicleLocation, VehicleNumber

A form 'frmEnterDisposition' allows the user to add a new disposition.

From the form 'frmEnterDisposition' I want to take the value of the field
'Disposition' and enter this value into the 'CurrentDisposition' field of
the
table 'tblVehicles' and specifically into the row that matches the
VehicleNumber from the field "VehicleNumber'

I hope this answers your question. If not, could you recommend some other
way to do this?

Cheers


:

Well, I'd need a lot more information than you've given. Table
structure,
field names, how you're using this and so on.

However, if you want to find the most current value, you can use an
aggregate query (totals query). You would use the Max function (NOT the
Last function) on a date field in the record. But I have to warn you,
it's
not easy to return other fields from the record. For an illustration of
this, go to my website (www.rogersaccesslibrary.com) and look for a
sample
called "MaxQueryProblem.mdb". It will show the problem and two
solutions
for it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks Roger

What I need to do is list vehicles that are 'available' . My
dispositions
table holds all the dispositions for all vehicles. What I am now
wondering
is if I should be inserting the last disposition into the
'CurrentDisposition' in the tblVehicles instead. Thar way there will
only
be
one record with the current disposition for each vehicle.

I am open to suggestions.

Cheers



:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber] & _
" And [Disposition] = 'Available'")

But this does not make sense to me. If you are trying to return the
value
of Dispostion, but you are including a value for it in the criteria,
the
only value it will ever return is "Available".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Is it possible to have two arguement to the DLast method?

I need to modify:

DLast("[Disposition]","tblDispositions","[VehicleNumber]=" &_
[Forms]![frmVehicleDispositions]![VehicleNumber])

to include where

'Disposition' = "Available"



Thanks in advance