Access 2003 Help Please !

[John H]

Hi,

I have a database in Access 2003 which records and reports information
on a weekly basis. One of the fields is called "Date_Out", which is
populated by data. On the form there is a button which once clicked,
writes 'todays' date in another field called "Date_In".
I am trying to write a procedure behind a button which:

1). Checks the database
2). If there is a value in "Date Out" and "Date_in", then
3). write a value called "Completed", into the completed field for
each record.

Any help on this will be greatly appreciated.

Regards,

John.

 

[Steve Schapel]

John,

What you are proposing is easy enough to accomplish. However, on the
face of your description, this would probably be regarded as an invalid
process. If "Completed" is a function of there being a valid entry in
both the Date_In and Date_Out fields, then "Completed" is a calculated
or derived value. We automatically know whether a record is Completed
or not, simply by an examination of the Date_In and Date_Out fields.
Therefore, we do not need to have a separate field to store this value,
and it is redundant data if we do so. In terms of database design,
then, we would advise against it. Instead, whenever you need to
reference the value "completed", for your purposes on form or report,
you can provide it "on the fly". This can either be done via a
calculated field in the query that the form or report is based on, or
else via a calculated expression in the Control Source of a control on
the form or report.

 

[John H]

John,

What you are proposing is easy enough to accomplish. However, on the
face of your description, this would probably be regarded as an invalid
process. If "Completed" is a function of there being a valid entry in
both the Date_In and Date_Out fields, then "Completed" is a calculated
or derived value. We automatically know whether a record is Completed
or not, simply by an examination of the Date_In and Date_Out fields.
Therefore, we do not need to have a separate field to store this value,
and it is redundant data if we do so. In terms of database design,
then, we would advise against it. Instead, whenever you need to
reference the value "completed", for your purposes on form or report,
you can provide it "on the fly". This can either be done via a
calculated field in the query that the form or report is based on, or
else via a calculated expression in the Control Source of a control on
the form or report.

--
Steve Schapel, Microsoft Access MVP







- Show quoted text -

Steve,

Thankyou very much for your feedback, im afraid I am a access
'newbie', if I am to understand you correctly you are simply advising
me to change my report query to ignore any records where the
"Date_Out" and "Date_In" feilds are complete. If this is so, can you
please advise on how the query should look ?

Again, kind regards,

John.

 

[Pieter Wijnen]

SELECT A.*, Not (IsNull(A.Date_in) Or IsNull(A.Date_out)) As Completed
FROM MyTable A

to include the Completed "Field"

SELECT A.*
FROM MyTable A
WHERE (IsNull(A.Date_in)=True Or IsNull(A.Date_out)=True)

to exclude the Completed records

HTH

Pieter

 

[Guest]

Dear Pieter,

I am not a SQL expert, and I use the query template (query design view) to
code a query, how would I do it? I am guessing that I could say Is Not Null
in both date criteria areas to get the completed (Or "> 01/01/1900" in both
criteria areas for the date fields)? But to get the incomplete where one or
the other date field Is Null? I don't know how to do a "or" between two
fields in a query without customizing/hard coding the SQL code of the query?
Is this a task that can only be done by hard coding the SQL?

Thanks,
Steve

 

[John W. Vinson]

I am not a SQL expert, and I use the query template (query design view) to
code a query, how would I do it? I am guessing that I could say Is Not Null
in both date criteria areas to get the completed (Or "> 01/01/1900" in both
criteria areas for the date fields)? But to get the incomplete where one or
the other date field Is Null? I don't know how to do a "or" between two
fields in a query without customizing/hard coding the SQL code of the query?
Is this a task that can only be done by hard coding the SQL?

It's certainly possible to design the query in either the grid or the SQL
window. You can put criteria on *separate lines* of the query grid go get OR
logic.

A couple of points: criteria of IS NULL or IS NOT NULL will work (and are the
best way to check for nullity); a Date/Time value should be delimited with #
marks; and a criterion

#01/01/1900#

will succeed only if there actually is a date stored in the field and it's
greater than that criterion. A NULL value will fail *ANY* comparison except
for IS NULL.

John W. Vinson [MVP]

 

[Steve Schapel]

John,

Just to try and summarise some of the others' comments...

If you are using the design view of a query, you can put this:
Is Not Null
.... in the Criteria of both the Date_In and Date_Out columns. This
query will return all Completed records.

You can put this:
Is Null
.... in the Criteria of the Date_In column, and put:
Is Null
.... in the *next row down* ( Or ) in the query design grid in the
Date_Out column. This query will return all Uncompleted records.

You could alternatively do this: Put no criteria in the Date_In and
Date_Out fields. In the Field row of a blank column in the query design
grid, put like this:
Completed: [Date_In] Is Not Null And [Date_Out] Is Not Null
If you run this query, this new calculated field will show whether the
record is Completed or not. To get only the Completed records, you
could put this in the Criteria of this column:
-1
.... and to get only the Uncompleted records, you could put this in the
Criteria of this column:
0

You could alternatively do this: Put no criteria in the Date_In and
Date_Out fields. In the Field row of a blank column in the query design
grid, put like this:
Completed: IIf(IsNull([Date_In]+[Date_Out]),Null,"Completed")
If you run this query, this new calculated field will show whether the
record is Completed or not. To get only the Completed records, you
could put this in the Criteria of this column:
"Completed"
.... and to get only the Uncompleted records, you could put this in the
Criteria of this column:
Is Null

You could alternatively do this: Base your form or report on the table.
Put an unbound textbox on the form or report. In the Control Source
of this textbox, you could put like this:
=IIf(IsNull([Date_In]+[Date_Out]),"Uncompleted","Completed")
.... or:
=IIf([Date_In] Is Null Or [Date_Out] Is Null,"Uncompleted","Completed")
This textbox will then show the status of each record.

These are just a few examples of how you can work with this data.

 

[Guest]

John Vinson,


Yes, you just woke me up about the extra line in the grid for criteria "or"
But that or is pertaining to the same data field, not a second data field? I
couldn't put a [A.Date_in]
citeria IsNull
Or IsNull[A.Date_out] in the grid space below A.Date_in ??

But how would you code using the grid criteria, as in John H's task where he
wants to see the uncompleted where one or the other date fields is null?

Thanks,
Steve

 

[John W. Vinson]

Yes, you just woke me up about the extra line in the grid for criteria "or"
But that or is pertaining to the same data field, not a second data field? I
couldn't put a [A.Date_in]
citeria IsNull
Or IsNull[A.Date_out] in the grid space below A.Date_in ??

Same field, different fields - they both work fine.

Don't use the IsNull() VBA function, though. Use

IS NULL

IS NOT NULL

just like that, no parentheses, with the blanks.

But how would you code using the grid criteria, as in John H's task where he
wants to see the uncompleted where one or the other date fields is null?

Put a criterion of

IS NULL

under the field that you want to be Null, and

IS NOT NULL

under the field that you want to be not-Null. If you put them on the same line
you'll get records which have (say) a Date_In but no Date_Out; if you put them
on separate lines you'll get all records which have a Date_In (whatever the
value of Date_Out might be), combined with all records which do not have a
Date_Out.

John W. Vinson [MVP]