Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old October 28th, 2016, 04:15 AM
Anathil Anathil is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2016
Posts: 2 Anathil User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 26 m 27 sec
Reputation Power: 0
Combine query for specific year and query for between two years

Hi everyone

I am working on a user form where you as part of the filtering process can enter a single year or an interval between two years.

I have three unbound fields
1- FindYearSpecific
2- FindYearStart
3- FindYearEnd

...and I am using the query builder cause I need to query other fields as well, so I would like to keep doing that instead of writing VBA (but that might be a mistake, I don't know...)

To find the specific year I have the following code: Like[Forms]![frmSearchGeneral].[FindYearSpecific] & "*"

This works just fine and give me all the records if I don't specify a year. This is exactly what I want

Then I have the following code for the year range: Between IIf([Forms]![frmSearchGeneral].[FindYearStart] Is Null,#01-01-1000#,[Forms]![frmSearchGeneral].[FindYearStart]) And IIf([Forms]![frmSearchGeneral].[FindYearEnd] Is Null,#31-12-9999#,[Forms]![frmSearchGeneral].[FindYearEnd])
A little explanation seems in order cause this is long...

In my top field in the query builder (Field) I have - FindYears: Year ([Date])
I use the extreme dates to make sure that I get all records if the field in the user form is left empty.

This works just fine most of the time. And I am not sure why it is only most of the time.
I have to write in both the fields before the code execute correctly. After that when I manually clears the fields everything works a cording to plan but if I use code to clear the fields like: me.FindYearStart = "" I don't get any records when I run my query.

And on top of that, my real problem is, that I seem unable to combine the two criteria to get anything useful :S

Just to sum up:
I need a query that do the following
1- Checks if I am asking for a specific year
if Yes, then return all records for that year - Then nothing else
if No, continue to 2
2- Checks if I am asking for a year range
If FindYearStart is empty, then returns all records form the beginning of time until year entered in FindYearEnd.
If FindYearEnd is empty, then returns all records form FindYearStart to the end of time.
If there is a year in both fields, then returns the range asked for

I think I am almost there but I really need some help to get the rest of the way.

Thank you in advance

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Combine query for specific year and query for between two years


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


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

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap