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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Limiting A Query To A Dropdown Value., Access 2013    
 
   
Doug Galayda
post Jul 17 2017, 02:20 PM
Post#1



Posts: 210
Joined: 19-May 10
From: Springfield, Illinois


UA,
I am assignING a combo box(drop down) value to an expression
Expr2: (Nz([Forms]![FrmActionReport]![ComboActionSiteId]))

And then use that expression to limit the query return to just that one id
Field: fkSiteId
Table: Dbo_tblPermits
Criteria [Expr2]

~~This is not working, what is the right way to do this?
--will try to add in the bb code to make this more readable.

SQL
SELECT dbo_tblPermits.NAME, dbo_tblPermits.pkLog_No, dbo_tblPermits.MAILED, dbo_tblPermits.FINAL_ACT, dbo_tblPermits.P_TYPE, dbo_tblPermits.F_TYPE, dbo_tblPermits.REVIEWER, dbo_tblPermits.GAU_REV, dbo_tblPermits.fkSiteID, (Nz([Forms]![FrmActionReport]![ComboActionSiteId])) AS Expr2
FROM dbo_tblPermits WHERE (((dbo_tblPermits.pkLog_No) Like '%') AND ((dbo_tblPermits.fkSiteID)=[Expr2]))
ORDER BY dbo_tblPermits.pkLog_No DESC , dbo_tblPermits.fkSiteID DESC;

This post has been edited by Doug Galayda: Jul 17 2017, 02:28 PM
Go to the top of the page
 
theDBguy
post Jul 17 2017, 02:28 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,013
Joined: 19-June 07
From: SunnySandyEggo


Hi Doug,

How about something like?

...AND ((dbo_tblPermits.fkSiteID)=Nz(Forms!FrmActionReport.ComboActionSiteID,0)))

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Doug Galayda
post Jul 17 2017, 03:03 PM
Post#3



Posts: 210
Joined: 19-May 10
From: Springfield, Illinois


SQL
SELECT dbo_tblPermits.NAME, dbo_tblPermits.pkLog_No, dbo_tblPermits.MAILED, dbo_tblPermits.FINAL_ACT, dbo_tblPermits.P_TYPE, dbo_tblPermits.F_TYPE, dbo_tblPermits.REVIEWER, dbo_tblPermits.GAU_REV, dbo_tblPermits.fkSiteID, (Nz([Forms]![FrmActionReport]![ComboActionSiteId])) AS Expr2
FROM dbo_tblPermits WHERE (((dbo_tblPermits.pkLog_No) Like '%') AND ((dbo_tblPermits.fkSiteID)=Nz(Forms!FrmActionReport.ComboActionSiteID,0)));


I replaced the end of the query(in SQL mode) with that code and now get a "Enter Parameter Value" pop up questionnaire on
"Forms!FrmActionReport!ComboActionSiteId"
This post has been edited by Doug Galayda: Jul 17 2017, 03:10 PM
Go to the top of the page
 
theDBguy
post Jul 17 2017, 03:08 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,013
Joined: 19-June 07
From: SunnySandyEggo


Did you make sure the form is open when you run the query?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Doug Galayda
post Jul 17 2017, 03:15 PM
Post#5



Posts: 210
Joined: 19-May 10
From: Springfield, Illinois


Yes Sir, I hit a button on a menu,
Then the button generates another form (frmHistoryReport) report driver,
Then I select a site_id from the drop down and then I hit the Run History Report button
Its code runs the report
DoCmd.OpenReport "rptHistory", acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdFitToWindow
The report runs the query to pull the data.
but Id from the drop down is not getting fed into the query
This post has been edited by Doug Galayda: Jul 17 2017, 03:19 PM
Go to the top of the page
 
GroverParkGeorge
post Jul 17 2017, 03:20 PM
Post#6


UA Admin
Posts: 30,971
Joined: 20-June 02
From: Newcastle, WA


Where is the backend?

From where are you running this SELECT statement?

--------------------
Go to the top of the page
 
theDBguy
post Jul 17 2017, 03:21 PM
Post#7


Access Wiki and Forums Moderator
Posts: 71,013
Joined: 19-June 07
From: SunnySandyEggo


Hi Doug,

Are you saying the original query you posted did not ask for a parameter? If so, please double-check my spelling to match what you had before. I just wanted you to move or copy the expression to the criteria.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Doug Galayda
post Jul 17 2017, 04:11 PM
Post#8



Posts: 210
Joined: 19-May 10
From: Springfield, Illinois


The backend database is on SQL server 2008
but I made a query on the access side to serve the report.
and the report has the query listed as the record source in its property sheet.
thanks!
This post has been edited by Doug Galayda: Jul 17 2017, 04:31 PM
Go to the top of the page
 
Doug Galayda
post Jul 17 2017, 04:15 PM
Post#9



Posts: 210
Joined: 19-May 10
From: Springfield, Illinois


No I am saying that it is still generating a parameter value pop up because
it is not receiving a value from the site id drop down
I added your suggested code the query in SQL mode
Let me double check everything and re-post the SQL and upload a snippet.png
--I think I have it mixed up, sorry.
Thanks
This post has been edited by Doug Galayda: Jul 17 2017, 04:19 PM
Go to the top of the page
 
theDBguy
post Jul 17 2017, 04:33 PM
Post#10


Access Wiki and Forums Moderator
Posts: 71,013
Joined: 19-June 07
From: SunnySandyEggo


Okay, if the original query (before my suggested changes) was asking for a parameter, then you'll have to fix it first. Double-checking the spellings might show the problem. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Doug Galayda
post Jul 17 2017, 04:44 PM
Post#11



Posts: 210
Joined: 19-May 10
From: Springfield, Illinois


Sorry, I had the wrong combo box control name in the query
--now I am getting a parameter pop up asking for the Forms!FrmActionReport.ComboHistorySiteId
(if I type the 0018010001 number in the parameter pop up, it generates the report
Can you assist?
Thanks!
SQL
SELECT dbo_tblPermits.NAME, dbo_tblPermits.pkLog_No, dbo_tblPermits.MAILED, dbo_tblPermits.FINAL_ACT, dbo_tblPermits.P_TYPE, dbo_tblPermits.F_TYPE, dbo_tblPermits.REVIEWER, dbo_tblPermits.GAU_REV, dbo_tblPermits.fkSiteID
FROM dbo_tblPermits WHERE (((dbo_tblPermits.pkLog_No) Like '%') AND ((dbo_tblPermits.fkSiteID)=Nz([Forms]![FrmActionReport].[ComboHistorySiteID],0)));

This post has been edited by Doug Galayda: Jul 17 2017, 05:44 PM
Attached File(s)
Attached File  Snippet_7_17_2017_543pm.PNG ( 13.31K )Number of downloads: 1
 
Go to the top of the page
 
Doug Galayda
post Jul 17 2017, 05:01 PM
Post#12



Posts: 210
Joined: 19-May 10
From: Springfield, Illinois


Can you assist?
I have the form and the field name corrected in the query,

Nz([Forms]![FrmHistoryReport].[ComboHistorySiteId],0)
(and the above is directly under the fkSiteId Column,

I no longer am getting a parameter value screen!
--But I am getting a blank report
and the site id value I have in the title bar says
#Type!
Thanks!
This post has been edited by Doug Galayda: Jul 17 2017, 05:55 PM
Go to the top of the page
 
projecttoday
post Jul 17 2017, 09:15 PM
Post#13


UtterAccess VIP
Posts: 8,601
Joined: 10-February 04
From: South Charleston, WV


What is the data type of dbo_tblPermits.fkSiteID? Is that field ever equal to 0?
What does [Forms]![FrmActionReport].[ComboHistorySiteID] return?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Doug Galayda
post Jul 18 2017, 08:54 AM
Post#14



Posts: 210
Joined: 19-May 10
From: Springfield, Illinois


Thanks for you input:

1) the data type is nvarchar
2) the return is nvarchar #Type!

I was using a drop down from a good working one I had in another system
The example system had a autonumber and number data types in two columns used in the combo/drop down.
But the two columns I am using in this system are both nvarchar!!
(I am completely rebuilding a old .adp access data project in a new database)
these are the columns I have to work with, and I would like to have a drop down on the site_id
(I did not design this table, the first column is a date, and there is no unique key in the table)
LogNo(nvarchar(8), null)
SiteNo(nvarchar(10), null)
So how to make it work with the columns I have?
So I need to change the ,0) to , Null) ?
This post has been edited by Doug Galayda: Jul 18 2017, 09:45 AM
Go to the top of the page
 
projecttoday
post Jul 18 2017, 09:20 AM
Post#15


UtterAccess VIP
Posts: 8,601
Joined: 10-February 04
From: South Charleston, WV


You say when you enter 0018010001 as a parameter, it works. That suggests to me that that is a referemce number from your work and is a text field since ID fields don't have leading zeroes. Ordinarily, what we call ID fields are table identifiers and are numerical and never null. Of course, you can call any field ...ID if you want. The point is don't compare the reference number field to the table ID field.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Doug Galayda
post Jul 18 2017, 10:01 AM
Post#16



Posts: 210
Joined: 19-May 10
From: Springfield, Illinois


You are right, fkSiteId was a nvarchar 10

When I pull up a fkSiteId on the combo/drop down, it has leading zeroes, and
it is displaying the fkSiteId column values from the same table I want to report on.

This is my dropdown select:
SELECT [dbo__Active_w/Action].pkLog_No, [dbo__Active_w/Action].fkSiteID FROM [dbo__Active_w/Action] ORDER BY [dbo__Active_w/Action].fkSiteID;
**both columns are nvarchar on SQL server.

So I want to take the return value from the drop down and match it to the same
Value in the table, using it to point the query to one id (a nvarchar text value),
and supply the query for the report.

The fkSiteId column(nvarchar 10) is what I want to match the return from the dropdown
up against.

This is my query: qryHistory, that supplies the report

SELECT dbo_tblPermits.NAME, dbo_tblPermits.pkLog_No, dbo_tblPermits.MAILED, dbo_tblPermits.FINAL_ACT, dbo_tblPermits.P_TYPE, dbo_tblPermits.F_TYPE, dbo_tblPermits.REVIEWER, dbo_tblPermits.GAU_REV, dbo_tblPermits.fkSiteID
FROM dbo_tblPermits
WHERE (((dbo_tblPermits.pkLog_No) Like '%') AND ((dbo_tblPermits.fkSiteID)=Nz([Forms]![FrmHistoryReport].[ComboHistorySiteId],null)));

It is not error-ing out, but is returning a #type! Value to the field
I use to display the fkSiteId in the title of the report.

="Site Id: " & [fkSiteId]

This post has been edited by Doug Galayda: Jul 18 2017, 10:46 AM
Go to the top of the page
 
projecttoday
post Jul 18 2017, 11:08 AM
Post#17


UtterAccess VIP
Posts: 8,601
Joined: 10-February 04
From: South Charleston, WV


You need to check to see what [Forms]![FrmHistoryReport].[ComboHistorySiteId] returns. Combo boxes (drop downs) return the "Bound Column".

QUOTE
You are right, fkSiteId was a nvarchar 10


If that's the case, then it differs from the naming convention.

The preferred way is to match on id fields, the table identifiers, rather than the fields that the user sees.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
tina t
post Jul 18 2017, 11:28 AM
Post#18



Posts: 5,182
Joined: 11-November 10
From: SoCal, USA


QUOTE
This is my dropdown select:
SELECT [dbo__Active_w/Action].pkLog_No, [dbo__Active_w/Action].fkSiteID FROM [dbo__Active_w/Action] ORDER BY [dbo__Active_w/Action].fkSiteID;

okay, your combobox control's RowSource has two columns. what is the setting of the combo's BoundColumn property?

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
Doug Galayda
post Jul 18 2017, 11:40 AM
Post#19



Posts: 210
Joined: 19-May 10
From: Springfield, Illinois


Bound column = 1
Limit to list = Yes
Allow Value List edits = Yes
I did try switching it to no. 2
and ran it and it showed the log number 2017-136
then switched it back.

If there were data returned by the query the
=fkSiteId field would populate.
but since no data, that's why the #type!
thanks!
I do see the fkSiteId in the drop down,
it is feeding that value into the query that it is not accomplishing.
or the query is not set to pick up that value correctly.
when I run the report, I get a blank report.
This post has been edited by Doug Galayda: Jul 18 2017, 11:51 AM
Go to the top of the page
 
projecttoday
post Jul 18 2017, 12:25 PM
Post#20


UtterAccess VIP
Posts: 8,601
Joined: 10-February 04
From: South Charleston, WV


Is this the actual query:

CODE
SELECT dbo_tblPermits.NAME, dbo_tblPermits.pkLog_No, dbo_tblPermits.MAILED, dbo_tblPermits.FINAL_ACT, dbo_tblPermits.P_TYPE, dbo_tblPermits.F_TYPE, dbo_tblPermits.REVIEWER, dbo_tblPermits.GAU_REV, dbo_tblPermits.fkSiteID, (Nz([Forms]![FrmActionReport]![ComboActionSiteId])) AS Expr2
FROM dbo_tblPermits WHERE (((dbo_tblPermits.pkLog_No) Like '%') AND ((dbo_tblPermits.fkSiteID)=Nz(Forms!FrmActionReport.ComboActionSiteID,0)));


?
Why the Nz? Why the Like '%'?

A combo with 2 columns doesn't have a column 2 because the first column is 0.

If you execute this

CODE
Dim str1 as String
str1 = Forms!FrmActionReport.ComboActionSiteID,0)


what is the value in str1?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    19th November 2017 - 10:50 AM