Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 21st, 2005, 12:20 PM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help! Date query Problem

I created a query to show the countdown of expiry date for membership
I drag "RenewDate" field From tblMember. then i define "ExpiryDate" and "Countdown" field in the query like following:

ExpiryDate: DateSerial(Year([RenewDate])+1,Month([RenewDate]),Day([RenewDate])-1)

Countdown: (DateDiff("m",Date(),[ExpiryDate]))

I run the query, everything is ok like can calculate the "ExpiryDate" and show how many month is left.

However, when i put ">=12" in Countdown's criteria. and run the query, the message popup and want me to enter "ExpiryDate" parameter.

Can anyone help me to solve the probl. thank you very much.

  #2 (permalink)  
Old November 21st, 2005, 06:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try using:
Code:
(DateDiff("m",Date(),DateSerial(Year([RenewDate])+1,Month([RenewDate]),Day([RenewDate])-1)
))
instead. Put the entire expression in the column rather than referencing a separate calculated column




I am a loud man with a very large hat. This means I am in charge
  #3 (permalink)  
Old November 21st, 2005, 09:54 PM
Authorized User
 
Join Date: Nov 2005
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much for your valuable help .....it works !

  #4 (permalink)  
Old November 22nd, 2005, 05:39 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Say, I am curious about this expression:

DateSerial(Year([RenewDate])+1,Month([RenewDate]),Day([RenewDate])-1)

What if the Renew Date is December 1, 2005? Your expression will evaluate to:

December 0, 2006

How does that get resolved in Access? Do you get any errors? Wouldn't a safer expression be:

DateAdd("d", -1, DateAdd("yyyy", 1, [RenewDate]))


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division


Similar Threads
Thread Thread Starter Forum Replies Last Post
date query problem naveed77 Beginning VB 6 5 March 4th, 2008 05:43 PM
Problem in Date Format in query imagetvr Pro VB 6 1 February 4th, 2008 06:03 PM
problem with the parameter date in a mdx query olmouy SQL Language 0 May 30th, 2007 05:32 AM
Date Query Problem hugh@kmcnetwork.com SQL Server 2000 2 March 18th, 2004 04:31 PM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





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