Solved

Querydef parameters

Posted on 2001-06-15
12
679 Views
Last Modified: 2008-03-10
A user can set two parameters on my form - one is text, the other a date.
An event opens a recordset for an existing query. This query is based on another query which takes 2 parameters - [myCourse] and [myDate] the data types of these parameters has been set in qryManualCount (which qryManualCount2 uses)
Code below;
Dim dbs As Database
Dim qdf1 As QueryDef

Set dbs = CurrentDb
Set qdf1 = dbs.QueryDefs![qryManualCount2]
qdf1.Parameters![myCourse] = Me!myCourse
qdf1.Parameters![myDate] = Me!txtDate

qdf1.OpenRecordset
Me!lstCourse.RowSource = qdf1.SQL

The problem is I get a dialog asking for a value for the [myDate] parameter even though I can see an appropriate value for that parameter when I inspect qdf1 using the debug window.

If I enter the date in the dialog I get the correct data, but cancelling/OKing the dialog means that nothing is returned.

Viv
0
Comment
Question by:vivn
  • 3
  • 3
  • 3
  • +3
12 Comments
 
LVL 1

Expert Comment

by:Doobs
ID: 6194786
Every thing looks okay. The only thing you might try is

"qdf1.Execute"
rather than
"qdf1.OpenRecordset"

OR
Try decalring the parameter vaiables in the subroutine and setting them using the
"Set (......) = " etc

Doobs
0
 
LVL 6

Accepted Solution

by:
Mach1pro earned 50 total points
ID: 6194819
See if this will react any differently

                    Set dbs = CurrentDb
                     Set qdf1 = dbs.QueryDefs![qryManualCount2]
                     qdf1.Parameters("myCourse") = Me!myCourse
                     qdf1.Parameters("myDate") = Me!txtDate
0
 

Author Comment

by:vivn
ID: 6195419
qdf1.Execute is not valid because this is a select query.
I'm not sure setting the parameters differently will have any effect - I can see that they have been set by inspecting.
The alternative naming convention for the parameters had no effect.

0
 
LVL 5

Expert Comment

by:TheMek
ID: 6195530
You might want to try formatting the date, so that it's clear to the program that it's a date field.

qdf1.Parameters![myDate] = Format(Me!txtDate, "mm/dd/yyyy")

Hope this helps,
   Erwin
0
 

Author Comment

by:vivn
ID: 6195880
Tried formatting the date but this doesn't work either.
Judging by the inspector the program does recognise this as a date (before I set the parameter data type to date/time I was getting a data type mismatch error)
0
 
LVL 6

Expert Comment

by:Mach1pro
ID: 6197263
vivn
Why not post the sql for your two queries?.  Maybe we could see something in there that you might be overlooking.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:vivn
ID: 6201753
SQL for first query (qryManualCount);
PARAMETERS [myCourse] Text, [myDate] DateTime;
SELECT tblSchedule.Course_id
FROM tblSchedule
WHERE (((tblSchedule.Course_id)=[myCourse]) AND ((tblSchedule.Course_date)>[myDate]) AND ((tblSchedule.Slot)="1") AND ((tblSchedule.Cancelled)="No"));
SQL for second query (qryManualCount2);
PARAMETERS [myCourse] Text, [myDate] DateTime;
SELECT qryManualCount.Course_id, Count(qryManualCount.Course_id) AS Total
FROM qryManualCount
GROUP BY qryManualCount.Course_id;
0
 
LVL 6

Expert Comment

by:Mach1pro
ID: 6203898
Try removing the [myDate] parameter from your second query.  I don't see any reason why you need it and that may be what the program is calling.
0
 

Expert Comment

by:amp072397
ID: 6742528
vivn:

You've logged in recently. Kindly return to and address this question.

thanks!
amp
community support moderator
0
 

Expert Comment

by:amp072397
ID: 6758954
It's time to clean up this topic area and that means taking care of this question. Your options at this point are:

1. Award points to the Expert who provided an answer, or who helped you most. Do this by clicking on the "Accept Comment as Answer" button that lies above and to the right of the appropriate expert's name.

2. PAQ the question because the information might be useful to others, but was not useful to you. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

3.  Ask Community Support to help split points between participating experts.  Just comment here with details.

4.  Delete the question because it is of no value to you or to anyone else.  To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

If you elect for option 2, 3 or 4, just post comment with details here and I'll take it from there. We also request that you review any other open questions you might have and update/close them.  Display all your question history from your Member Profile to view details.

PLEASE DO NOT AWARD THE POINTS TO ME.
____________________________________________

Hi Experts:

In the event that the Asker does not respond, I would very much appreciate your opinions as to which Expert ought to receive points (if any) as a result of this question.  Likewise, you can also suggest that I PAQ or delete the question.

Experts, please do not add further "answer" information to this question.  I will be back in about one week to finalize this question.

Thank you everyone.

amp
community support moderator
0
 

Expert Comment

by:amp072397
ID: 6782913
vivn:

You have several open questions:

http://www.experts-exchange.com/jsp/qShow.jsp?qid=20242297
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20136062
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20088613
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20027514

To assist you in your cleanup, I'm providing the following guidelines:

1.  Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.

2.  Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.

3.  When grading, be sure to read:
http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.

4.  Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
CS:  http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.

5.  If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.

Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!


thanks!
amp
community support moderator

2/6
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6943857
Per recommendation, force-accepted by
Netminder
CS Moderator
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Error in query expression 3 37
data analyst 3 50
append to an ms access field 6 26
Running Total in Access 4 22
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now