Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-18-2016, 01:25 PM   #1
casaler
Newly Registered User
 
Join Date: Jul 2016
Posts: 24
Thanks: 0
Thanked 1 Time in 1 Post
casaler is on a distinguished road
Parameter Query Question

Hi, I am casaler (Robert) and self taught in most Office programs. I do have a question (need help on) regarding using a user's input data on a form, and it doesn't match a record in a table, then convert it to "-" which is on the table. Suggestions? If need be, I could provide my database(s). Thanks in advance.

casaler is offline   Reply With Quote
Old 07-18-2016, 01:56 PM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,320
Thanks: 0
Thanked 728 Times in 713 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Parameter Query Question

If the record does not exist, then
Do nothing
Else
Update the record to a negative value?

vID=Dlookup("ID","table","[id]=" & txtBoxID)
If not isNull(vID) then docmd.openquery "quNegVal"
Ranman256 is offline   Reply With Quote
Old 07-18-2016, 02:21 PM   #3
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,289 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
I would use DCount() to check for the existence of a record.
Code:
If DCount("*", "table", <criteria>) then
   'the record or records exist
Else
   'the record was not found
End If

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 07-19-2016, 05:38 AM   #4
casaler
Newly Registered User
 
Join Date: Jul 2016
Posts: 24
Thanks: 0
Thanked 1 Time in 1 Post
casaler is on a distinguished road
Re: Parameter Query Question

Is this close (Query1 result contains the record that I am trying to match on tblBuild which is from the user's input)?
If
(SELECT Query1.VIN, Query1.MDL_OP
FROM Query1 INNER JOIN tblBuild ON Query1.VIN = tblBuild.VIN
WHERE (((Query1.MDL_OP)=[tblBuild].[POPCD])) = 1 (yes)
Then
Do Nothing
Else
UPDATE Query1 INNER JOIN tblBuild ON Query1.VIN = tblBuild.VIN SET
WHERE (((Query1.MDL_OP)=[tblBuild].[POPCD]));
casaler is offline   Reply With Quote
Old 07-19-2016, 05:54 AM   #5
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,507
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Parameter Query Question

Quote:
Originally Posted by casaler View Post
Hi, I am casaler (Robert) and self taught in most Office programs. I do have a question (need help on) regarding using a user's input data on a form, and it doesn't match a record in a table, then convert it to "-" which is on the table. Suggestions? If need be, I could provide my database(s). Thanks in advance.
What if it doesn't match because of a typo in the user's input? Would you still want to convert it to a "-"? I suggest uploading your database with an explanation of where the problem lies so that we can suggest alternatives. Typically the valid values are made available in a combo box. You could design is so that combo box has a "Other" selection that would put the dash in the table if nothing else applied.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 07-19-2016, 06:01 AM   #6
casaler
Newly Registered User
 
Join Date: Jul 2016
Posts: 24
Thanks: 0
Thanked 1 Time in 1 Post
casaler is on a distinguished road
Re: Parameter Query Question

I'll upload the database...is it done via the "link?"
casaler is offline   Reply With Quote
Old 07-19-2016, 06:20 AM   #7
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,507
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Parameter Query Question

Quote:
Originally Posted by casaler View Post
I'll upload the database...is it done via the "link?"
See this page for instructions on how to upload.

__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 07-19-2016, 06:31 AM   #8
casaler
Newly Registered User
 
Join Date: Jul 2016
Posts: 24
Thanks: 0
Thanked 1 Time in 1 Post
casaler is on a distinguished road
Re: Parameter Query Question

I upload my data database (GPT_Test) and a mini set of testing info. The goal of the database is to provide a percentage amount due based upon the repair type that is outside of the vehicle warranty coverage. I have started filtering out the possibilities, but I am stuck on how to add the Primary OP Code field; if the op code is "-" or not on the list, then make it "-", if it is, then use it. Query has the results and comes from the FRF_WITH_PNC table, MDL_OP field. Any thoughts, help is appreciated.
Attached Files
File Type: accdb GPT_TEST.accdb (1.88 MB, 33 views)
File Type: txt Tool_Testing_Info.txt (312 Bytes, 27 views)
casaler is offline   Reply With Quote
Old 07-19-2016, 06:50 AM   #9
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,507
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Parameter Query Question

Please upload a copy of the GPT_TBL_TEST.accdb backend database.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 07-19-2016, 06:59 AM   #10
casaler
Newly Registered User
 
Join Date: Jul 2016
Posts: 24
Thanks: 0
Thanked 1 Time in 1 Post
casaler is on a distinguished road
Re: Parameter Query Question

Sorry...forgot I had one...if you have any suggestions on streamlining or efficiency, then please comment...
Attached Files
File Type: accdb GPT_TBL_TEST.accdb (784.0 KB, 35 views)
casaler is offline   Reply With Quote
Old 07-19-2016, 08:11 AM   #11
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,507
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Parameter Query Question

This is going to take some time. It's not easy to see what you are doing. Currently I'm going through the code documenting it like:

Code:
stDocName = "qrdVINP_T"  'clear tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qrdEMTYPE" 'clear tblEMTYPE
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra1VINP_T"    'Append VIN1: Mid([VIN],5,1) to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
If you could summarize what this code does it might move things along.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 07-19-2016, 09:08 AM   #12
casaler
Newly Registered User
 
Join Date: Jul 2016
Posts: 24
Thanks: 0
Thanked 1 Time in 1 Post
casaler is on a distinguished road
Re: Parameter Query Question

All that is doing is decoding the VIN into various parts to query against the Master FRF table; the Emission delete and append is doing the same thing, but for emission data. All the queries work okay. The next section I am trying to incorporate is the model op code; my concern is if a user enters an op code that is not on the list (FRF_With_PNC) associated with the specific FRF code.
casaler is offline   Reply With Quote
Old 07-19-2016, 09:10 AM   #13
casaler
Newly Registered User
 
Join Date: Jul 2016
Posts: 24
Thanks: 0
Thanked 1 Time in 1 Post
casaler is on a distinguished road
Re: Parameter Query Question

My computer availability time is hit-and-miss at the moment; I will not be available for the next 5 hours.
casaler is offline   Reply With Quote
Old 07-19-2016, 12:17 PM   #14
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,507
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Parameter Query Question

As I said before typically an input like the Primary OPCD would be a combo box that had the distinct values that were possible. You can try this if you want. You would:
  1. Open the frmBuildInfo in design view
  2. Right click on the POPCD combo box
  3. In the menu click Change to and then Combo Box
  4. Goto the properties of the POPCD combo box
  5. In Data tab click on the Row Source and click on the ellipsis on the right
  6. In the Show Table click on the FRF_WITH_PNC and click Add
  7. Double click on the MDL_OP to add it as a field
  8. Switch to the SQL View (Right click on the title frmBuildInfo)
  9. Add the keyword DISTINCT after SELECT (See SQL Below)
  10. Close the query and save the form

Code:
SELECT DISTINCT FRF_WITH_PNC.MDL_OP
FROM FRF_WITH_PNC;
If you now look in the drop down of the combo box you will see that along with AT31AA and AT30AA an empty string is a possibility. I noticed your system relies on them (empty strings) which isn't good. Since you can't see the difference between a Null and an empty string they will give you all kinds of headaches. I suggest you get rid of them. If you need a placeholder, then use something you can see.

While normally the Primary OPCD combo box would be the distinct values of the MDL_OP field this may not be enough and this appears to be constrained by the trip in Query1 in the circle from Query2.POPCD to FRF_WITH_PNC.MDL_OP then from FRF_WITH_PNC.PNC to PART_PNC_ADHOC.PNC and finally PART_PNC_ADHOC.Part_NO to PFP5. These tables don't have primary keys and I'm not sure what kind of results you can expect.

If you can give us an explanation of these table and their data I think we could be more helpful.

Also after studying this for hours I still don't understand your original question. Where and why would code put a dash in somewhere?
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve

Last edited by sneuberg; 07-19-2016 at 04:40 PM.
sneuberg is offline   Reply With Quote
Old 07-19-2016, 04:38 PM   #15
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,507
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Parameter Query Question

You could eliminate a lot of clutter by executing SQL in code using CurrentDb.Execute rather than doing it in stored queries. Below I show the code for the first four of those statements. I included the others as I already put comments in them that would help you if you want to do this.

I'm really curious as to how all these different parts of a VIN could ever be related to the single field VIN-PFX. Would you share that with us?

Code:
'stDocName = "qrdVINP_T"  'clear tblVINP
'DoCmd.OpenQuery stDocName, acViewNormal, acEdit
CurrentDb.Execute "Delete tblVINP.VINP FROM tblVINP;", dbFailOnError


'stDocName = "qrdEMTYPE" 'clear tblEMTYPE
'DoCmd.OpenQuery stDocName, acViewNormal, acEdit
CurrentDb.Execute "Delete tblEMTYPE.EMTYPE FROM tblEMTYPE;", dbFailOnError

'stDocName = "qra1VINP_T"    'Append VIN1: Mid([VIN],5,1) to tblVINP
'DoCmd.OpenQuery stDocName, acViewNormal, acEdit
CurrentDb.Execute "INSERT INTO [tblVINP] (VINP) VALUES ('" & Mid(Me.VIN, 5, 1) & "')", dbFailOnError

'stDocName = "qra2VINP_T"    'Append VIN10: Left([VIN],8) & " " & Mid([VIN],10,2) to tblVINP
'DoCmd.OpenQuery stDocName, acViewNormal, acEdit
CurrentDb.Execute "INSERT INTO [tblVINP] (VINP) VALUES ('" & Left(Me.VIN, 8) & " " & Mid(Me.VIN, 10, 2) & "')", dbFailOnError


stDocName = "qra3VINP_T"   'Append VIN2C: Mid([VIN],4,2) tp tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra4VINP_T"    'Append VIN3: Mid([VIN],5,3) to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra5VINP_T"    'VIN3C: Mid([VIN],4,3) to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra6VINP_T"    'Append VIN4: Mid([VIN],4,4) to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra7VINP_T"    'Append VIN4C: Mid([VIN],4,2) & "    " & Mid([VIN],10,2)
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra8VINP_T"    'Append VIN5A: Left([VIN],5) to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra9VINP_T"    'Append VIN5: Left([VIN],4) & " " & Mid([VIN],6,1)
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra10VINP_T"   'Append VIN6: Left([VIN],1) & "    " & Mid([VIN],5,2)
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra11VINP_T"   'Append VIN9: Left([VIN],7) & "  " & Mid([VIN],10,2)
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra12VINP_T"   'Append VIN10: Left([VIN],8) & " " & Mid([VIN],10,2)
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra13VINP_T"   'Append VIN11: Left([VIN],11) to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qra14VINP_T"   'Append VIN12: "-" to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

stDocName = "qraEMTYPE" 'Append Emmision to EMTYPE in tblEMTYPE
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Reply

Tags
access 2013 , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Parameter Query question JeffreyDavid Forms 7 11-19-2008 06:29 PM
Query Parameter question rkrause Queries 3 07-09-2008 11:49 AM
Parameter Query Question muirchez Queries 3 11-24-2006 03:56 AM
Parameter query value question razorking Queries 2 08-29-2006 07:53 PM
Parameter Query Question cstanley Queries 3 05-29-2002 08:07 PM




All times are GMT -8. The time now is 03:53 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World