Trouble with designing a query

[Electricman]

Hey all. What I need to do is calculate Days Old, which I can do, but I need
it to be Zero or preferably null if the "ShippingDate" is not null. I still
need all the records to show or this would be easy. My SQL is as follows:

SELECT Table1.RMANumber, Table1.QNumber, Table1.RxDate, Table1.QDate,
Table1.CatNumber, Table1.RtnQty, Table1.TechUPDDate, Table1.CSDispoUPDDate,
Table1.ShippingDate, Date()-[RxDate] AS DaysOld
FROM Table1
WHERE (((Table1.RMANumber) Is Not Null));

ACC2003
Thanks for your help!

 

[John Welch]

try:
iif(isnull(table1.shippingdate),Date()-[RxDate],null) as DaysOld
and skip the where clause
-
-John

 

[Ofer]

Try this, using IIf, with datediff

SELECT Table1.RMANumber, Table1.QNumber, Table1.RxDate, Table1.QDate,
Table1.CatNumber, Table1.RtnQty, Table1.TechUPDDate, Table1.CSDispoUPDDate,
Table1.ShippingDate, IIf(Table1.ShippingDate Is Null,
DateDiff("d",[RxDate],Date()),Null) AS DaysOld
FROM Table1
WHERE (((Table1.RMANumber) Is Not Null));

 

[Electricman]

Let me ask 2 questions at once though I am in the wrong "category."
My second question is this: I am using this information to populate a list
box on my form. However, I need to filter this to only list the records with
a common RMANumber as the form this list resides on. Each RMANumber may have
several QNumbers. I.E., the listbox would only display records with that
RMA, whether 1 or 25. Conversely, the listbox would allow a record to be
selected and open that QNumber in the main form. Basically, the listbox is a
"report" of the status of the items under this "RMA". Since I am seeing all
of them at once, I'd like to be able to click from one to the other to edit
as necessary. Thanks in advance for your help. (Even if it is a swift kick
for approaching this the wrong way!!!)

 

[John Vinson]

Let me ask 2 questions at once though I am in the wrong "category."
My second question is this: I am using this information to populate a list
box on my form. However, I need to filter this to only list the records with
a common RMANumber as the form this list resides on. Each RMANumber may have
several QNumbers. I.E., the listbox would only display records with that
RMA, whether 1 or 25. Conversely, the listbox would allow a record to be
selected and open that QNumber in the main form. Basically, the listbox is a
"report" of the status of the items under this "RMA". Since I am seeing all
of them at once, I'd like to be able to click from one to the other to edit
as necessary. Thanks in advance for your help. (Even if it is a swift kick
for approaching this the wrong way!!!)

You can base the Listbox on a query referencing the RMA control. Use a
criterion

=[Forms]![YourFormName]![RMAControlName]

on the RMA field of the table containing the QNumbers.

You'll need one line of VBA code: Requery the QNumber listbox in the
AfterUpdate event of the RMA control.

John W. Vinson[MVP]

 

[Electricman]

John,
Thanks for the great reply. I added the criterion to the query and it works
great. However, I have not been successful in the requery part. I am using
a Cbo to pick a "Q" Number. Therefore the form generically opens on the
first record. My list box successfully shows all Q's associated with the RMA
number of this record. However, it never changes from this. I've added:
DoCmd.Requery [RMAListBox]
to the after update event of the RMANumber control, then tried the QNumber
control and even the form. Still stays the same. Everything in the form
updates, except the RMAListBox. But, if I open the query, it shows the
correct info of the record that is opened. Any clue what I am doing wrong?

Let me ask 2 questions at once though I am in the wrong "category."
My second question is this: I am using this information to populate a list
box on my form. However, I need to filter this to only list the records with
a common RMANumber as the form this list resides on. Each RMANumber may have
several QNumbers. I.E., the listbox would only display records with that
RMA, whether 1 or 25. Conversely, the listbox would allow a record to be
selected and open that QNumber in the main form. Basically, the listbox is a
"report" of the status of the items under this "RMA". Since I am seeing all
of them at once, I'd like to be able to click from one to the other to edit
as necessary. Thanks in advance for your help. (Even if it is a swift kick
for approaching this the wrong way!!!)

You can base the Listbox on a query referencing the RMA control. Use a
criterion

=[Forms]![YourFormName]![RMAControlName]

on the RMA field of the table containing the QNumbers.

You'll need one line of VBA code: Requery the QNumber listbox in the
AfterUpdate event of the RMA control.

John W. Vinson[MVP]

 

[Electricman]

One more note, John. I just discovered that if I manually update the field,
then it all works great. Apparently, even though the form's info is updated
by the choosing of a Q number to include the RMANumber field, it is not seen
as an update, therefore AfterUpdate does not work.

John,
Thanks for the great reply. I added the criterion to the query and it works
great. However, I have not been successful in the requery part. I am using
a Cbo to pick a "Q" Number. Therefore the form generically opens on the
first record. My list box successfully shows all Q's associated with the RMA
number of this record. However, it never changes from this. I've added:
DoCmd.Requery [RMAListBox]
to the after update event of the RMANumber control, then tried the QNumber
control and even the form. Still stays the same. Everything in the form
updates, except the RMAListBox. But, if I open the query, it shows the
correct info of the record that is opened. Any clue what I am doing wrong?

Let me ask 2 questions at once though I am in the wrong "category."
My second question is this: I am using this information to populate a list
box on my form. However, I need to filter this to only list the records with
a common RMANumber as the form this list resides on. Each RMANumber may have
several QNumbers. I.E., the listbox would only display records with that
RMA, whether 1 or 25. Conversely, the listbox would allow a record to be
selected and open that QNumber in the main form. Basically, the listbox is a
"report" of the status of the items under this "RMA". Since I am seeing all
of them at once, I'd like to be able to click from one to the other to edit
as necessary. Thanks in advance for your help. (Even if it is a swift kick
for approaching this the wrong way!!!)

You can base the Listbox on a query referencing the RMA control. Use a
criterion

=[Forms]![YourFormName]![RMAControlName]

on the RMA field of the table containing the QNumbers.

You'll need one line of VBA code: Requery the QNumber listbox in the
AfterUpdate event of the RMA control.

John W. Vinson[MVP]

 

[John Vinson]

John,
Thanks for the great reply. I added the criterion to the query and it works
great. However, I have not been successful in the requery part. I am using
a Cbo to pick a "Q" Number. Therefore the form generically opens on the
first record. My list box successfully shows all Q's associated with the RMA
number of this record. However, it never changes from this. I've added:
DoCmd.Requery [RMAListBox]

That should be requerying the Q Number control - not requerying
itself!

to the after update event of the RMANumber control, then tried the QNumber
control and even the form. Still stays the same. Everything in the form
updates, except the RMAListBox. But, if I open the query, it shows the
correct info of the record that is opened. Any clue what I am doing wrong?

What is the Recordsource of the Form? and what are the Row Sources of
the RMA and Q controls? I may be misunderstanding your structure.

John W. Vinson[MVP]

 

[Electricman]

John,
The form's record source is Table1. There is no separate row source for the
QNumber or RMANumber fields.
Initially, there is another form where this data is entered, i.e.,
NewReturns. The form I am working in now is DispositionEntries and is a
three tab form populated by the table. The RMAListBox is based on a query to
retrieve specific info from the same table based on user selection. I use a
command button to open the form without any specificity as to record
selection. I am using 3 separate Cbos to select info from: One to select a
Q # that has not been completed by, in this case, Customer Service. So it
only lists those "unsigned." Second, to find any Q#, so that CS can look at
any record they need to. Third, to find a Q# based on an RMA, therefore
sorted by RMA. What I am trying to accomplish is to choose from any of these
and open that record and the RMAListBox to show all RMA's associated with
whatever record is chosen.
Am I to gather, that if I had based my entire form on a Query, then this
would be easy... simply requerying?
Also, John, I designed the RMAListBox to see records, but need it also to
choose records to show. If you have any wisdom as to how I can still do this
after the fact, I would greatly appreciate it. The problem with redoing it
is, I am viewing all the columns listed in my earlier post and it took a
while to fit them all neatly within the listbox and form! You've been very
helpful and I thank you very much for your insights and in my case, oft
necessary rebukes!!! Thanks.

John,
Thanks for the great reply. I added the criterion to the query and it works
great. However, I have not been successful in the requery part. I am using
a Cbo to pick a "Q" Number. Therefore the form generically opens on the
first record. My list box successfully shows all Q's associated with the RMA
number of this record. However, it never changes from this. I've added:
DoCmd.Requery [RMAListBox]

That should be requerying the Q Number control - not requerying
itself!

to the after update event of the RMANumber control, then tried the QNumber
control and even the form. Still stays the same. Everything in the form
updates, except the RMAListBox. But, if I open the query, it shows the
correct info of the record that is opened. Any clue what I am doing wrong?

What is the Recordsource of the Form? and what are the Row Sources of
the RMA and Q controls? I may be misunderstanding your structure.

John W. Vinson[MVP]

 

[John Vinson]

John,
The form's record source is Table1. There is no separate row source for the
QNumber or RMANumber fields.

A Combo Box or Listbox (on the Form) MUST have a RowSource. I thought
you were trying to update the rowsource for the QNumber combo box to
select only QNumbers valid for the selected RMA - was I
misundrstanding?

Initially, there is another form where this data is entered, i.e.,
NewReturns. The form I am working in now is DispositionEntries and is a
three tab form populated by the table. The RMAListBox is based on a query to
retrieve specific info from the same table based on user selection. I use a
command button to open the form without any specificity as to record
selection. I am using 3 separate Cbos to select info from: One to select a
Q # that has not been completed by, in this case, Customer Service. So it
only lists those "unsigned." Second, to find any Q#, so that CS can look at
any record they need to. Third, to find a Q# based on an RMA, therefore
sorted by RMA. What I am trying to accomplish is to choose from any of these
and open that record and the RMAListBox to show all RMA's associated with
whatever record is chosen.

Now I'm REALLY confused. These are three unbound QNumber combo boxes?
and you're - what - displaying the record's QNumber on the Form?

I'd understand "find a Q# based on an RMA" to mean that you're
selecting one single RMA number and displaying all of the Q Numbers
associated with that RMA. Evidently I'm not understanding you
correctly.

Am I to gather, that if I had based my entire form on a Query, then this
would be easy... simply requerying?
Also, John, I designed the RMAListBox to see records, but need it also to
choose records to show. If you have any wisdom as to how I can still do this
after the fact, I would greatly appreciate it. The problem with redoing it
is, I am viewing all the columns listed in my earlier post and it took a
while to fit them all neatly within the listbox and form! You've been very
helpful and I thank you very much for your insights and in my case, oft
necessary rebukes!!! Thanks.

I would be inclined to use a Listbox to *select* from a (small) list
of valid values, not to display data. A continuous Subform is much
more flexible for the display task.

I'll go back and reread the thread and see if I've been
misinterpreting your requests all along (which would neatly explain
why my advice isn't working!)

John W. Vinson[MVP]

 

[Electricman]

John,
Can I send you a screenshot of the form? It would allow you to get an idea
of what I am attempting. I don't think I am explaining myself well.