VISIBLE ITEMS unbound list box

[Guest]

The following qry is an unbound list box...
What would I do to only have a requireddate show in the column if it is a
date greater than today. I don't want it to filter all of what is being shown
in all columns in the list. I just don't want past dates to be visible in the
list.

SELECT Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber,
qrySupplierIDLookup.SupplierName, Orders.RequiredDate
FROM Orders LEFT JOIN qrySupplierIDLookup ON Orders.SupplierID =
qrySupplierIDLookup.SupplierID
WHERE (((Orders.Inactive)=False))
ORDER BY Orders.PurchaseOrderNumber;

I don't know if this is even possible. Maybe I need to add another unbound
list box that only lists this criteria

 

[Guest]

Hi Imv,

You can try (untested) using a conditional IF statement. Something like this:

SELECT Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber,
qrySupplierIDLookup.SupplierName, IIF(RequiredDate >
Date(),[RequiredDate],"") AS [Date Required]
FROM ....


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thanks I tried...
what you had and then tried
IIF([RequiredDate] > Date(),[RequiredDate],"") AS [RequiredDate]

but I got not valid syntax and AS was highlighted?

Hi Imv,

You can try (untested) using a conditional IF statement. Something like this:

SELECT Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber,
qrySupplierIDLookup.SupplierName, IIF(RequiredDate >
Date(),[RequiredDate],"") AS [Date Required]
FROM ....


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

The following qry is an unbound list box...
What would I do to only have a requireddate show in the column if it is a
date greater than today. I don't want it to filter all of what is being shown
in all columns in the list. I just don't want past dates to be visible in the
list.

SELECT Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber,
qrySupplierIDLookup.SupplierName, Orders.RequiredDate
FROM Orders LEFT JOIN qrySupplierIDLookup ON Orders.SupplierID =
qrySupplierIDLookup.SupplierID
WHERE (((Orders.Inactive)=False))
ORDER BY Orders.PurchaseOrderNumber;

I don't know if this is even possible. Maybe I need to add another unbound
list box that only lists this criteria

 

[Van T. Dinh]

The part you have after AS is the same as an existing Field name which is
not acceptable by JET. Note that Tom used an Alias different from the Field
name which should work fine.

--
HTH
Van T. Dinh
MVP (Access)

Thanks I tried...
what you had and then tried
IIF([RequiredDate] > Date(),[RequiredDate],"") AS [RequiredDate]

but I got not valid syntax and AS was highlighted?

Hi Imv,

You can try (untested) using a conditional IF statement. Something like
this:

SELECT Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber,
qrySupplierIDLookup.SupplierName, IIF(RequiredDate >
Date(),[RequiredDate],"") AS [Date Required]
FROM ....


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

The following qry is an unbound list box...
What would I do to only have a requireddate show in the column if it is
a
date greater than today. I don't want it to filter all of what is being
shown
in all columns in the list. I just don't want past dates to be visible
in the
list.

SELECT Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber,
qrySupplierIDLookup.SupplierName, Orders.RequiredDate
FROM Orders LEFT JOIN qrySupplierIDLookup ON Orders.SupplierID =
qrySupplierIDLookup.SupplierID
WHERE (((Orders.Inactive)=False))
ORDER BY Orders.PurchaseOrderNumber;

I don't know if this is even possible. Maybe I need to add another
unbound
list box that only lists this criteria

 

[Guest]

I know you guys know way more than I do but I entered

IIF(RequiredDate > Date(),[RequiredDate],"") AS [Date Required]

In the criteria of the RequiredDate of the qry and I still get syntax error.

Thanks

The part you have after AS is the same as an existing Field name which is
not acceptable by JET. Note that Tom used an Alias different from the Field
name which should work fine.

--
HTH
Van T. Dinh
MVP (Access)

Thanks I tried...
what you had and then tried
IIF([RequiredDate] > Date(),[RequiredDate],"") AS [RequiredDate]

but I got not valid syntax and AS was highlighted?

Hi Imv,

You can try (untested) using a conditional IF statement. Something like
this:

SELECT Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber,
qrySupplierIDLookup.SupplierName, IIF(RequiredDate >
Date(),[RequiredDate],"") AS [Date Required]
FROM ....


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

The following qry is an unbound list box...
What would I do to only have a requireddate show in the column if it is
a
date greater than today. I don't want it to filter all of what is being
shown
in all columns in the list. I just don't want past dates to be visible
in the
list.

SELECT Orders.OrderID, Orders.OrderDate, Orders.PurchaseOrderNumber,
qrySupplierIDLookup.SupplierName, Orders.RequiredDate
FROM Orders LEFT JOIN qrySupplierIDLookup ON Orders.SupplierID =
qrySupplierIDLookup.SupplierID
WHERE (((Orders.Inactive)=False))
ORDER BY Orders.PurchaseOrderNumber;

I don't know if this is even possible. Maybe I need to add another
unbound
list box that only lists this criteria

 

[Guest]

Hmmm.....I'm not sure what you are doing wrong. Try the following query in
the sample Northwind database:

SELECT Orders.OrderID, Orders.OrderDate, Orders.RequiredDate,
IIF(RequiredDate > Date(),[RequiredDate],"") AS [Date Required]
FROM Orders;


Change some of the RequiredDate values to future dates.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Well I did it again and now it works... Thanks!

If you have time will you look at my previous posted question:

SUBTRACT true ckbx value in Report

And direct me to somewhere there is an example of what I am trying to do if
you know. Those that have answered have not given me code to try. They say
things but I don't understand how to turn what they are saying into proper
syntax.

Thanks!!

Hmmm.....I'm not sure what you are doing wrong. Try the following query in
the sample Northwind database:

SELECT Orders.OrderID, Orders.OrderDate, Orders.RequiredDate,
IIF(RequiredDate > Date(),[RequiredDate],"") AS [Date Required]
FROM Orders;


Change some of the RequiredDate values to future dates.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I know you guys know way more than I do but I entered

IIF(RequiredDate > Date(),[RequiredDate],"") AS [Date Required]

In the criteria of the RequiredDate of the qry and I still get syntax error.

Thanks

 

[Guest]

Glad you got it working.

If you have time will you look at my previous posted question...

I'll have a look if you post the hyperlink to the thread, so that I don't
have to go searching for it. Since you are using the MS Web Portal to post
your messages, you should see an icon just to the left of the printer icon.
If you first navigate to the thread in question, and then click on this icon,
the URL will be copied to your clipboard. Return to this thread and post a
reply with the pasted URL (Ctrl V to paste). No promises that I'll be able to
help you, but I will take a look if you help me find your thread easier.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

http://www.microsoft.com/office/com...cb714f-8543-45fe-8802-2baba19a8db9&sloc=en-us

Hope this is it...