ACCESS FEED: RE: [MS_AccessPros] Adding a IIF statement for a date query

Jumat, 30 Desember 2011

RE: [MS_AccessPros] Adding a IIF statement for a date query

Art-

Put code after the OpenForm to test txtBillDueDate and set the Caption and Visible properties on the newly opened form as needed.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Waco, Texas <yee haw>)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Friday, December 30, 2011 4:14 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Adding a IIF statement for a date query

John,

Had some issue with the underling query but figured it out.

SELECT tblVendorTransaction.VendorTransID, tblVendorTransaction.VendorID, Sum(tblVendorSubTrans.Amount) AS Amount, tlkpTransactionType.TransactionTypeName, tblVendorTransaction.ReferenceNo, tblVendorSubTrans.UnitID, tlkpAccountType.AccountTypeName, tblVendorTransaction.Memo, tblVendorTransaction.BillDueDate
FROM ((tblVendorTransaction INNER JOIN tblVendorSubTrans ON tblVendorTransaction.VendorTransID = tblVendorSubTrans.VendorTransID) LEFT JOIN tlkpTransactionType ON tblVendorTransaction.TransactionTypeID = tlkpTransactionType.TransactionTypeID) LEFT JOIN tlkpAccountType ON tblVendorSubTrans.AccountTypeID = tlkpAccountType.AccountTypeID
GROUP BY tblVendorTransaction.VendorTransID, tblVendorTransaction.VendorID, tlkpTransactionType.TransactionTypeName, tblVendorTransaction.ReferenceNo, tblVendorSubTrans.UnitID, tlkpAccountType.AccountTypeName, tblVendorTransaction.Memo, tblVendorTransaction.BillDueDate;

So its working now with your expression. I was having thing double up.

Any thank you for that code!

This is next because it has to do with the OVERDUE statement. On the form that contains txtDueDate I have a command button called cmdTransDetail which opens the frmTransDetail form which displays the record for this transaction. If the trans record is OVERDUE I would like to update a label called lblMessage which is on the frmTRansDetail telling the user that this bill is overdue. Current the lblMessage is hidden on frmTransDetail form.

Thnak you,
Art

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, December 30, 2011 2:37 PM
Subject: RE: [MS_AccessPros] Adding a IIF statement for a date query

Art-

In the Control Source for txtBillDueDate put:

=IIf([BillDueDate] < Date(), "OVERDUE", Format([BillDueDate], "mm/dd/yyyy")

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Waco, Texas <yee haw>)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art
Sent: Friday, December 30, 2011 2:08 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Adding a IIF statement for a date query

I have the following query:

SELECT tblVendorTransaction.VendorTransID, tblVendorTransaction.VendorID,
tlkpTransactionType.TransactionTypeName, tblVendorTransaction.BillDate,
tlkpAccountType.AccountTypeName, tblVendorTransaction.Memo,
tblVendorSubTrans.Amount, tblVendorSubTrans.UnitID,
tblVendorTransaction.BillDueDate
FROM ((tblVendorTransaction INNER JOIN tblVendorSubTrans ON
tblVendorTransaction.VendorTransID = tblVendorSubTrans.VendorTransID) LEFT JOIN
tlkpTransactionType ON tblVendorTransaction.TransactionTypeID =
tlkpTransactionType.TransactionTypeID) LEFT JOIN tlkpAccountType ON
tblVendorSubTrans.AccountTypeID = tlkpAccountType.AccountTypeID;

This is the record source for a continuous form. On the form I have a field
called txtBillDate. WHat I am trying to do is if the BillDueDate is less than
todays date then in txtBillDueDate enter OVERDUE otherwise display the
BillDueDate.

Is this possible?

Thanks,

Art Lorenzini
Sioux Falls, SD

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links