UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Combo Box Issues With Row Source As A Query, Access 2016    
 
   
kreenbottles
post May 22 2019, 04:11 PM
Post#1



Posts: 8
Joined: 21-May 19



I have a report with some combo boxes. The report shows car VINS and dates for maintenance as combo boxes (some dates past and some in the future). Each combo box needs to have a query to show up blank (or return no results works as well) if the date is past today, and return the date in the field if it's in the future.

My code looks like this:

SELECT tblMaster.MaintReqDate
FROM tblMaster
WHERE tblMaster.MaintReqDate IS NOT NULL and tblMaster.MaintReqDate >= DATE();

The control source is set to MainReqDate, which is a Date/Time Column. I've tried using Now(), CURRENT_TIMESTAMP, and CURDATE().

When running the report, I get the date showing up regardless of if it's past or in the future. The null part works correctly though and nothing shows up if it's empty in the table.

I'm not sure if it's a problem with my SQL, or if I need to make it a text box and use a different equation.
Go to the top of the page
 
theDBguy
post May 22 2019, 04:22 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,582
Joined: 19-June 07
From: SunnySandyEggo


Hi. I didn't think the Is Not Null part is necessary. The >=Date() part should be enough, I would think. However, it doesn't explain why you would see past dates from today. Are you able to share a sample database?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
kreenbottles
post May 22 2019, 04:57 PM
Post#3



Posts: 8
Joined: 21-May 19



Unfortunately I am not able to share, as it contains some information that is private in nature.

So if a select statement returns nothing, it should be blank right?

I tried also blanking out the control source, but it just changed the box to unbound. Does a text box take SQL? If so, maybe it will work correctly with the code as the control source? I currently have it as the row source in the combo box.
Go to the top of the page
 
theDBguy
post May 22 2019, 05:01 PM
Post#4


Access Wiki and Forums Moderator
Posts: 75,582
Joined: 19-June 07
From: SunnySandyEggo


No, Textboxes don't take SQL statements. Only combos, listboxes, and subforms do. As for "confidential" info, maybe you could take them out and simply insert a couple of test data instead?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
kreenbottles
post May 22 2019, 06:18 PM
Post#5



Posts: 8
Joined: 21-May 19



Sent you a PM, thanks!
Go to the top of the page
 
theDBguy
post May 22 2019, 06:42 PM
Post#6


Access Wiki and Forums Moderator
Posts: 75,582
Joined: 19-June 07
From: SunnySandyEggo


Just saw it...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
kreenbottles
post May 22 2019, 06:50 PM
Post#7



Posts: 8
Joined: 21-May 19



I found a workaround.

I changed the boxes from combo boxes to text boxes and used the following code:

=IIF([MaintReqDate]>=Date(),[MaintReqDate],"")

All of my testing this works perfect so far. Hopefully this can help somone else who is running into a similar problem and uses the forum search (how many of us do that though?)
Go to the top of the page
 
theDBguy
post May 22 2019, 07:24 PM
Post#8


Access Wiki and Forums Moderator
Posts: 75,582
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th June 2019 - 12:31 PM