Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 31st, 2006, 10:24 AM
Registered User
 
Join Date: Oct 2006
Location: Roanoke, Virginia, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with access query

Ok, I have a query that I'm almost complete. It is pulling the data from dbo_ticket_responses, and my query is named mynewxtab. I am completely new to Access and this is my first time working in it, so please pardon the ignorance and bare with me.

The query pulls information from the table, which consist of data from the past 90 days. It shows the data when ran in a table format, which works perfect. The problem is, it shows the data from the entire 90 days, and I want it to prompt me when I run the query to specify dates (e.g. "From what date?" "To what date") and the end user (call floor DPS specialist) can input whatever date they want and it only return those results. This is a project I need to get done but I am stuck. I'm enclosing a screenshot of what a user on another forum told me to put to make it work yet it isn't working. Can someone please tell me what I am doing wrong? Thank you so much!!!!
<img src="http://www.dreamscapetech.net/~vups/almost.JPG">

Reply With Quote
  #2 (permalink)  
Old January 4th, 2007, 04:37 PM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Location: Boston, MA, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Your screenshot does not display and copying the link to the address box simply gives me a "Page Not Found". You should paste your SQL code into your message so that we all know what you are talking about. Keep in mind that this is an Access VBA forum so that code is what we deal with. If you only create queries thru the Query Designer (as most of us do) you have one of 2 options for this:

     1) Make a text representation of the query screen. Yes you can do it! Early query-by-example systems were text-based to begin with.

     2) Open the SQL window (its one of the 3 viewing options for queries) and copy & paste the code from that to your message. If you receive a useful reply as complete SQL code you can copy & paste this from the message into the SQL window of a new query and then look at it in Design view if you want.


-Phil-
Reply With Quote
  #3 (permalink)  
Old January 5th, 2007, 08:29 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

If you want the user to select a date range, use a form, and then a button to run the query.

Make two combo boxes on the form, one cboDateStart, and one cboDateEnd and have them look up to your table so that only actual available dates are pulled. Set the Unique Values to Yes.

Then in the criteria line of the query (in design view) put this:

Start date column:
Criteria: [Forms]![frmMyFormName].[cboDateStart]

End date column:
Criteria: [Forms]![frmMyFormName].[cboDateEnd]

You can also do some business rules on the button to make sure a date is selected by using this:

If IsNull(Me.cboDateStart) Or Me.DateStart = "" Then
   Msgbox "Please select a start date", vbCritical
   Exit Sub
End If
If IsNull(Me.cboDateEnd) Or Me.DateEnd = "" Then
   Msgbox "Please select an end date", vbCritical
   Exit Sub
End If



mmcdonal
Reply With Quote
  #4 (permalink)  
Old January 5th, 2007, 08:29 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Salisbury, Wiltshire, United Kingdom.
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

Without seeing your picture but having read your query, it shouldbe fairly simple...

1. Go to Query tab on the Main DB window
2. Click New
3. Select Design view
4. Add your table(s) and set up whatever relationships you feel it might need
5. Double click the fields in the tables you want to appear in your results... this will add them to the grid below the diagram.
6. Locate in the grid, the field you would like to restrict using your users data
7. Go to the "Criteria" line of the field in 6
8. Type: [From What Date?] or [To what date?] as appropriate
9. Run the query... you may have to play around with < and > for the dates to get it right... but it is fairly straightforward.

Basically anything inside [ ] in your qeury criteria will be a reference that the query will have to find... whether it is a form reference or some text... if text then it will open a dialog box. There are a myriad of ways to handle this... this is the most straight-forward, but also brings many pitfalls.

Good luck, hih

Lee
Reply With Quote
  #5 (permalink)  
Old January 5th, 2007, 08:30 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I should mention that in the combo boxes, you only want the dates and not the PK, so that the bound column is that date.

mmcdonal
Reply With Quote
  #6 (permalink)  
Old January 5th, 2007, 08:30 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Salisbury, Wiltshire, United Kingdom.
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

mmcdonal way is much better... use that! less pitfalls
Reply With Quote
  #7 (permalink)  
Old January 25th, 2007, 05:49 PM
Authorized User
 
Join Date: Nov 2006
Location: Seattle, WA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The other thing you can do in a select query;

under the field that you want to restrict to a range is do the following, if it's a date field

In the criteria section of the field;

Between [Enter 1st Date] And [Enter 2nd Date]

or

you can get a certain ranges of date; i.e.

1. >=Date()-14

this will give you the dates for the past two weeks.



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Access query from a webpage? oldmainframehack Access VBA 1 August 13th, 2007 12:29 PM
MS Access Query rsm42 ASP.NET 1.0 and 1.1 Basics 2 March 20th, 2007 12:32 PM
Access Query gre_smi SQL Server 2000 2 January 11th, 2006 08:59 AM
need access query kale_tushar Access 2 January 3rd, 2005 05:26 AM
Access: need a Query. please help me... alienscript Access VBA 1 December 14th, 2003 01:02 PM



All times are GMT -4. The time now is 07:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.