none
data validation for entries into a parameter query RRS feed

  • Question

  • Hello All, I have a parameter query where the end user is asked to enter a date. I wish to verify that the date is a particular day of the week, to match the starting day of my clients business week, which is Wednesday. I have tried using an IF command with the following code, but it just won't work:

    =Weekday([sevendays].[datefield])<>4

    To explain, the sevendays table has only one field called datefield, and a parameter query successfully asks the user to enter a date with then becomes the first record in this table.

    Can someone please either tell me why this doesn't work and how to fix it, or any other way to verify an entry made to a parameter query?

    Thanks.

    Monday, June 20, 2016 5:09 PM

Answers

  • IF Weekdays([sevendays].[datefield]) <> 4

    THEN

    MESSAGEBOX  (bla bla bla)

    You will have to use control in an unbound dialogue form as the parameter.  You can then reference the control to validate it before calling the query.  You cannot reference the column in the table in the way you appear to be attempting.


    Ken Sheridan, Stafford, England

    • Marked as answer by KareninMD Tuesday, June 28, 2016 4:06 PM
    Tuesday, June 21, 2016 5:57 PM

All replies

  • Hi. Can you please post the actual SQL statement of your query? The expression you posted is not exactly an IF statement. Also, if you're trying to make sure the user enters a valid date, you might have to switch to using a Form for the user's input, so you can validate it before running the query. Just my 2 cents...
    Monday, June 20, 2016 6:53 PM
  • Hi KareninMD,

    I agree with .theDBguy that you should post your query so that we can try to understand and try to give you some suggestion.

    on this stage I can only suggest you to visit some links.

    Examples of using dates as criteria in Access queries

    if you are using simple query then you can try to use below example query to get the day.

    SELECT  Format(mdate,"dddd") from mydate;

    it will return you a day from the date. you can modify as per your requirement.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 21, 2016 1:48 AM
    Moderator
  • This is clearly a situation where the parameter should be a reference to a control in an unbound dialogue form, not a crude system generated prompt.  You can then very easily validate the value in the control's BeforeUpdate event procedure and set the return value of the procedure's Cancel argument to True if the date does not fall on a Wednesday.  A command button on the form will then execute an INSERT INTO or UPDATE statement (It's unclear from your post which you are doing).

    Ken Sheridan, Stafford, England

    Tuesday, June 21, 2016 4:01 PM
  • OK, maybe I wasn't clear.  Let me try again.  First, I am NOT a VBA programmer.  I have a Macro that calls a query that deletes all the records out of the sevendays table.  Then it calls an append query that is a simple parameter query that prompts the user to enter a date, and then appends that date as the first record in the table.  Then, in the Macro I have a line of code that reads:

    IF Weekdays([sevendays].[datefield]) <> 4

    THEN

    MESSAGEBOX  (bla bla bla)

    and I keep getting an error msg that states:

    Microsoft cannot find the name 'sevendays' you entered in the expression.

    I have tried both the period and the exclamation point between the table name and the field name, and neither works.

    Does this help explain my problem?

    Tuesday, June 21, 2016 4:06 PM
  • Hi. Are you referring to a data macro or a UI macro? If UI, how it is executed?
    Tuesday, June 21, 2016 4:18 PM
  • In VBA its easy to perform that task. Does it have to be a SQL query?

    Best regards, George

    Tuesday, June 21, 2016 4:24 PM
  • I don't know what you mean by "UI", but it is a macro that appears under Macros in my navigation pane.  It gets called from an "On Open" event on a form.
    Tuesday, June 21, 2016 4:29 PM
  • UI=User Interface -> a Form in this case

    Best regards, George

    Tuesday, June 21, 2016 4:32 PM
  • Karen, can I ask you if you use an iPhone?

    Best regards, George

    Tuesday, June 21, 2016 4:33 PM
  • Well, if you can instruct me how to write this in VBA then I guess it doesn't have to be a SQL query.  I have written an entire database, with approximately 30 tables, 40 queries, 50 forms and 20 reports (I know these numbers because I have just begun to write my documentation).  My client is pretty happy, and I think it is feature-rich with cascading combo boxes, and some pretty sophisticated logic, all without knowing VBA. 

    Thanks for any help you can be to me.

    Tuesday, June 21, 2016 4:35 PM
  • No, I don't have an iPhone.  Why do you ask?

    Tuesday, June 21, 2016 4:42 PM
  • Just a quality check. Never mind

    Best regards, George

    Tuesday, June 21, 2016 4:44 PM
  • But tell me this: why is ignorance important?

    "all without knowing VBA"


    Best regards, George

    Tuesday, June 21, 2016 4:46 PM
  • So let's recap - you have not answered my question, but instead only told me that the solution is easy to accomplish in VBA.  Then you ask me at a minimum off topic and at worst inappropriate questions about what type of phone I have, and then, it appears, call me ignorant.  So, I'd like to ask why you are even on this forum if you're not going to help people, but I have wasted enough time and energy on this conversation.
    Tuesday, June 21, 2016 5:07 PM
  • I don't know what you mean by "UI", but it is a macro that appears under Macros in my navigation pane.  It gets called from an "On Open" event on a form.

    Hi. You have a UI macro. Can you please post the complete macro? Thanks.
    Tuesday, June 21, 2016 5:27 PM
  • Oh, yes. I help people a lot. And they are grateful. But help cannot be received if you cannot comprehend the answer. You brag about being ignorant in VBA. Ok. Then I cannot help you.


    Best regards, George

    Tuesday, June 21, 2016 5:28 PM
  • IF Weekdays([sevendays].[datefield]) <> 4

    THEN

    MESSAGEBOX  (bla bla bla)

    You will have to use control in an unbound dialogue form as the parameter.  You can then reference the control to validate it before calling the query.  You cannot reference the column in the table in the way you appear to be attempting.


    Ken Sheridan, Stafford, England

    • Marked as answer by KareninMD Tuesday, June 28, 2016 4:06 PM
    Tuesday, June 21, 2016 5:57 PM
  • Hi George.B.Summers,

    this is now more then 2 times in 10 days I had suggest you not post any kind of irrelevant suggestions which are not helpful to solves the op's issue.

    I already told you that with this kind of post we lose the response from op and then threads are always stay opened.

    The other thing op never get the solution for his issue.

    and overall it creates a bad impression of our forum.

    so after discussing with my superiors we had mark your suggestion as Abusive.

    also op is not happy with this kind of post and don't want to see post like this.

    so we expect from you that you will not post this kind of suggestions in future.

    Regards

    Deepak

     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 22, 2016 6:19 AM
    Moderator
  • Hi KareninMD,

    we had mark the suggestion given by George.B.Summers as an Abusive.

    we had check the suggestion and find that it is not helpful at all.

    sorry for the inconvenience you had faced.

    please try to see the other replies and let us know it is helpful or not to solve your issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 22, 2016 6:23 AM
    Moderator
  • Deepak,

    I'd suggest you forward this post:

    https://social.msdn.microsoft.com/Forums/office/en-US/2b73f244-1e48-406f-acbc-05f53fee2346/dynamic-select-sub-query-syntax?forum=accessdev

    Search for: Good Morning Deepak
    See especially #7 and #9 below that.

    .. to your superiors.

    Your contributions are only copycats of someone trying to answer and your "own" answer is not helpful at all!


    Best regards, George

    Monday, June 27, 2016 4:50 AM
  • Hi Ken, and thanks!  After some figuring I got this to work.  I have a pretty complicated set of steps where the report calls a macro that calls several queries, and I had to find the exact right place to call the modal so that it could pass the date entered to the next query, but I got it to work!  Thanks again.
    Tuesday, June 28, 2016 4:09 PM