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
> Query Will Not Run, Access 2016    
 
   
ordnance1
post Jul 22 2019, 08:46 AM
Post#1



Posts: 643
Joined: 7-May 11



I have this simple query that will not run, it does nothing. The issue is with the Where statement and I don't understand the problem. If i run debug.Print forms![frm_Navigation]![NavigationSubform].form![cbo_ReportLookup].Column(1) in the immediate window it returns the correct value.

CODE
SELECT tbl_Profit_Loss.Report_ID, tbl_Profit_Loss.Setup_ID
FROM tbl_Profit_Loss
WHERE (((tbl_Profit_Loss.Report_ID)=[forms]![frm_Navigation]![NavigationSubform].[form]![cbo_ReportLookup].Column(1)));


Here is the SQL for the referenced cbo_ReportLookup

CODE
SELECT tbl_Profit_Loss.Setup_ID, tbl_Profit_Loss.Report_ID, [StartDate] & "  -  " & [EndDate] AS DateRange
FROM tbl_Profit_Loss
ORDER BY tbl_Profit_Loss.Report_ID DESC;
Go to the top of the page
 
GroverParkGeorge
post Jul 22 2019, 09:25 AM
Post#2


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


Is this a stored query? Or are you running it in VBA?

It matters.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ordnance1
post Jul 22 2019, 09:28 AM
Post#3



Posts: 643
Joined: 7-May 11



Stored query
Go to the top of the page
 
GroverParkGeorge
post Jul 22 2019, 09:39 AM
Post#4


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


Is the form open when you run it? Is there a value selected in the combo box when you run it?

"It does nothing..." means that it returns an empty recordset? or does it mean that it refuses to open?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ordnance1
post Jul 22 2019, 09:47 AM
Post#5



Posts: 643
Joined: 7-May 11



Yes the referenced form is open and has a value. Also if I run debug.Print forms![frm_Navigation]![NavigationSubform].form![cbo_ReportLookup].Column(1) in the immediate window it returns the proper value. And sorry I should have been more clear. The query does not open, no error msg no result (blank or otherwise).
Go to the top of the page
 
ordnance1
post Jul 22 2019, 09:55 AM
Post#6



Posts: 643
Joined: 7-May 11



As a quick test I added an unbound textbox on the form and used cbo_ReportLookup.Column(1) as it's controlsource and the value displays. I then referenced the textbox in the query and it runs as expected. I suppose I can hid the textbox and use it but still do not understand why the original plan does not work.
Go to the top of the page
 
GroverParkGeorge
post Jul 22 2019, 10:15 AM
Post#7


UA Admin
Posts: 35,508
Joined: 20-June 02
From: Newcastle, WA


To be honest, it's often hard to diagnose processes from afar. This is one of those cases. I would expect, for example, that the saved query would open, or raise an error, or open with errors or no records. The fact that it fails to open at all is hard to figure out.

Continued success with the project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ordnance1
post Jul 22 2019, 11:52 AM
Post#8



Posts: 643
Joined: 7-May 11



I appropriate your time. I was able to use the workaround of setting a textbox controlsource to the combobox column value and the referencing the textbox in the query.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 12:38 AM