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
52 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
7 Comments
 
LVL 7

Accepted Solution

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

Assisted Solution

by:PatHartman
PatHartman earned 250 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 250 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 24

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 24

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

919 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

16 Experts available now in Live!

Get 1:1 Help Now