I am new to this forum and wanted to say Hello to every one, and i hope i can help every one as much as they can help me.
I have made a query for weekly reports and i have used this to give me the weeks to look like what i wanted instead of just the number of week it is:
WeekPeriod: CStr([mydate]-Weekday([mydate],6)+1) & " - " & CStr([mydate]-Weekday([mydate],6)+7)
but becouse this comes back as text i group on this:
WeekStart:[mydate]-Weekday([mydate],6)+1
My Problem is that in my dates of Arrivals field that i am basing this off of, I have values that are empty(Null) but they need to stay that way, I don't want to count thoughts in the weekly report. I have tried a number of steps and keep getting either an #error or the big Invalid Use Of Null error every time I try to run this query. If any one could help on this problem it would be greatly appreciated
Thank You
7  12806 
Do you need the null value records? If not, can you filter them out ('Is Not Null')?
Have you tried using Nz (null to zero) - that should eliminate the null value error but may not give you a sensible result for WeeklyPeriod!
cheers-
James
I tried putting Is Not Null in critera but it gave me the same error, and i am not sure were to use the NZ.
I tried putting Is Not Null in critera but it gave me the same error, and i am not sure were to use the NZ.
You can put the Nz around your date expression: Nz([mydate])
everywhere you use it.
I'm not sure why the Is Not Null is not working. If you run the query with the [mydate] field displayed, are you getting any results where it is null? Or does the query not run at all?
James
The IS Not Null worked if i put the date of arrival (My date) in the query, but it shows double ex: if 2 ppl came in on 24-may-07 then it has to strings, its not grouping them together. and the NZ([Date of arrival]) on each one of them keeps giving me errors, missing operators mostly, even if i enclose it again with parentheses
Can you post the SQL of your query?
James
NeoPa 32,556
Expert Mod 16PB
The second parameter of Nz() is the default you want to use if it finds a Null value. You may want to set this to a standard date such as Date() (=Today).
Nz() will typically work by inference with strings or numbers. It may have more difficulty with a Date/Time value so you will need to provide the default parameter.
PS. It wouldn't hurt to post the SQL anyway ;)
NeoPa 32,556
Expert Mod 16PB
well i got it to work, with the Is Not Null fuction, I put it in the Weekstart string and it worked. So thank you very much for all the help. Saddly i have another small problem that doesn't make sence.
The new problem I have is that this was my last big thing, so I updated all of the data in my table and so all the reports do not have any records in them yet. So all of my total counts are 0 in my reports but instead of 0 they are Errors for some wierd reason, and i am not sure why at all, but my macros and some delete querys stoped working becouse it is returning an Error insted of 0. I have got caught way off gaurd with that.
Before I try to answer this one, I will split this into another thread ( Report Shows #Error When Recordset Empty). Things get messy and difficult to search when multiple questions are mixed in together.
MODERATOR.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: rawheiser |
last post by:
Behavior I found:
Invalid Column name in select embedded in a IN() clause appears to
return a NULL, and fails silently - completing the query giving
incorrect results.
Behavior I expected:
...
|
by: Martin Lacoste |
last post by:
Ok, before I headbutt the computer... don't know why when I add
criteria in a query, I get an 'invalid procedure call'. I also don't
know why after searching the help in access, the various access...
|
by: headware |
last post by:
I'm getting a weird problem in an Access query. I have a table that
contains a field calle F1 that's a 2 character text field. The first
character is always a number. What I'd like to do is find...
|
by: mpriem |
last post by:
I am developing a C# windows application which will act as an frontend
for a SQL 2000 database.
I am using a laptop with SQl 2000 workgroup edition (sp4). SQL is setup
to use windows...
|
by: David |
last post by:
Hi,
I have built a web application that will be a very high profile application.
We had tested it, demonstrated it and shown that it all works.
On a dress rehearsal run through, it failed...
|
by: mike |
last post by:
I have the following view definition
Column | Type | Modifiers
----------------+-----------------------+-----------
bcode | character varying(15) |
subhead ...
|
by: Roman Mashak |
last post by:
Hello, All!
I already posted my question and received valuable feedbacks, I changed my
code as was proposed here but still receive the same error of valgrind. SO,
the code is:
#define...
|
by: Yitzak |
last post by:
Hi spent a few hours on this one wrote a query that joined on results
of 2 other queries.
Qry3 using Qry1 and Qry2
When I used Qry1.FasText <cstr(Qry2.FasInteger) in the where clause
- got...
|
by: timber910 |
last post by:
Hello All,
I'm in need of help here. I have build my query in a query builder in access as a select query. Query runs fine. Changed it to a make table query. Query makes table ok. Copy and pasted...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
