Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

I would like the syntax to have an user enter a Store ID or Date in order to run a report?

Posted on 2016-10-14
7
Medium Priority
?
71 Views
Last Modified: 2016-11-19
I have a report of a form. I want to user to select a Store ID or a date to call up the report. I was able to set a date range to enter a query, which works well. The syntax I used was this. Between [beginning Date?] And [Ending Date?] I am looking for the correct syntax to make this work. I know I can't use "Between" because it's for a range. Do you know the syntax???
Thanks
Earl
0
Comment
Question by:Earl Wolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 1000 total points (awarded by participants)
ID: 41843836
use the Datediff command
0
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points (awarded by participants)
ID: 41843837
You would use the = sign.

A better method of passing arguments to a report is to enter them on the form that runs the report.  That way you can validate the arguments first and you will not be prompted multiple times to enter them as you are if you preview first and then print.

Some Examples


Where SomeField = Forms!yourform!SomeField

Where SomeDate Between Forms!yourform!startDate And Forms!yourform!EndDate

Where SomeField = Forms!yourform!SomeField OR (SomeDate Between Forms!yourform!startDate And Forms!yourform!EndDate)

Where SomeField = Forms!yourform!SomeField AND  (SomeDate Between Forms!yourform!startDate And Forms!yourform!EndDate)
0
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 1000 total points (awarded by participants)
ID: 41843843
or where datediff("d", [beginning Date], FindDate) > 0 and datediff("d", [Ending Date] , Finddate) < 0

I removed the "?" and used Finddate as the comparator.

sometimes between gives false results if the dates are on the same day but have different times
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41844592
Try..

WHERE ( [beginning Date] >= FindDate AND FindDate <= [Ending Date] )
0
 
LVL 9
ID: 41845380
I recommend having the report call a form (I know, it seems backwards at first - trust me) to prompt for selection criteria and build a Where clause.  See our free download Report Selection Techniques at http://www.jstreettech.com/downloads.  We use this technique for all our Access projects.

Cheers,
Armen
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41866437
@Author - A feedback will be appreciated.

Thank you!
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41894049
All valid comment
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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