473,325 Members | 2,442 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

IIF conditionals using static and form entries as a criteria for queries

1
Cant seem to get this syntax to work right. Have a form that allows users to enter a week ending date to query on, or they can leave it blank to use the default range of weeks, which is the last 3 weeks from today.
Here is the criteria statement I used:

Expand|Select|Wrap|Line Numbers
  1. IIf([Forms]![frmMoSel]![CBX_WED]=" ", >=Date()-21, [Forms]![frmMoSel]![CBX_WED])
default setting in CBX_WED is " ". query works if i enter a date in the form, but I cant get the static portion (ie the 3 week range) of the criteria to work when I leave the form blank. I also tried using the ISNULL condition instead of the " ", set the column as an expression, as a "where" setting, and as a "group by" settings, all with tthe same results, nothing. no errors, just opens and closes with no records showing. here is the sql portion of the syntax

Expand|Select|Wrap|Line Numbers
  1. HAVING (((NADTble.WE_DT)=IIf([Forms]![frmMoSel]![CBX_WED] = " ",Date()-21,[Forms]![frmMoSel]![CBX_WED])) AND ...
any help is appreciated
May 13 '09 #1
3 1209
Curtis Rutland
3,256 Expert 2GB
This isn't the appropriate forum for technical questions...
Please identify what language (or from the looks of it, which database) you are using so a moderator can move it to the proper forum.

Thanks,
insertAlias
MODERATOR
May 13 '09 #2
DonRayner
489 Expert 256MB
Expand|Select|Wrap|Line Numbers
  1. Date()-21
should be
Expand|Select|Wrap|Line Numbers
  1. DateAdd("d",-21,Date()),
I would also change up the IIf to check for anything other than a zero length string instead of checking for a zero length string.

Expand|Select|Wrap|Line Numbers
  1. IIf([Forms]![frmMoSel]![CBX_WED]>"",[Forms]![frmMoSel]![CBX_WED], >=DateAdd("d",-21,Date()))
May 13 '09 #3
NeoPa
32,556 Expert Mod 16PB
@Danmop
I would use WHERE clause rather than HAVING. This is understandable as it's the default provided in Access unless you select Where (in a grouped query).

An empty control will be Null. Not an empty string (""), and never a single space (as you've used).

You appear to be comparing a Date field with a single date 21 days ago, rather than searching for any date after that date (or more flexibly Between two specified dates).
May 13 '09 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Steve | last post by:
I am using Access 2000 and I have a DB that is currently running four different queries from some Forms and subforms. These queries each have a date field that are seperate from each other that are...
4
by: nick_faye | last post by:
hi, hope someone can help me. i am a newbie in creating queries and i want to create a query wherein i only get entries from my table where values of fields 2, 3 and 4 are not zeros. for...
3
by: I_was_here | last post by:
Hey if anyone is a query pro please showoff some knowledge thx. Ie: I have a table with : part price location qty 1 part repeats throughout the table and its price remains the same but it...
1
by: Aaron | last post by:
Hello fellow programmers, I am trying to run an append/update query from code, a command button on a form initiates the queries. the format i am using is; ...
3
by: Marcus | last post by:
Wondering if it is possible to use one form to open different queries at different times. As an example, I may have frmViewQueries that opens up when I click a command button on frmMain. So, the...
14
by: Darin | last post by:
I have a table that I want to delete specific records from based on data in other tables. I'm more familiar with Access '97, but am now using 2003, but the database is in 2000 format. In '97, I...
3
by: google | last post by:
I'm developing an application for use within my company in Access 2003. I'm new to '03, the application I did for my former employer was in '97. The two applications have similar functionality...
3
by: jgscott3 | last post by:
I have a number of queries that require various parameters. However, the parameters will change infrequently, so I do not want the user to have to respond to them every time they run the queries. ...
5
by: EricS | last post by:
Hi, Was wondering if anyone can help. I have multiple queries in a database. The selection criteria for these queries is always a time period I want to do the analysis by. Rather than having to go...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.