Tardy database

[ncdavis25 via AccessMonster.com]

Hi,

I'm having some troubles with a tardy database I'm creating, I created a
detention append query where I want to add all of the student tardies to
determine if they should receive a detention or not but it is not working.
when I run it, it request for me to enter a parameter value for Forms!
ScanEntry!PassSID, Forms!ScanEntry!PassDateTime, Forms!ScanEntry!
PassnMaxUnExc and then it says invalid use of Null.
The form is open and I ran the query, but I still get the Invalid use of Null
message. Where am I going wrong?


INSERT INTO DT ( SID, DTDateTime, ServeByDate, TotalLateCount )
SELECT DISTINCT Pass.SID, Max(Forms!ScanEntry!PassDateTime) AS Expr2, DLookUp
("[ServeByDate]","Dates - DT","[DTDate] = #" & Date() & "#") AS ServeByDate,
CInt(Forms!ScanEntry!PassnMaxUnExc) AS Expr1
FROM Pass
GROUP BY Pass.SID, DLookUp("[ServeByDate]","Dates - DT","[DTDate] = #" & Date
() & "#"), CInt(Forms!ScanEntry!PassnMaxUnExc);

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This:

Max(Forms!ScanEntry!PassDateTime)

is meaningless 'cuz there will be only 1 value in that form's Control.
Max() selects the maximum value from a list of values.

This:

CInt(Forms!ScanEntry!PassnMaxUnExc)

is useless if the value in PassnMaxUnExc is already an integer - just
use the form reference without the CInt() function.

This:

#" & Date() & "#"

does not require the # delimiters - just use the Date() function without
the # delimiter.

This:

"[ServeByDate]" and this "[DTDate]=

have incorrect syntax - they should be "ServeByDate" and "DTDate= -
without the brackets.

The following query is a better query. I changed the DLookup to a INNER
JOIN of the table [Dates - DT]. Since I don't know what the SID is (I'm
guessing StudentID), nor if it is in [Dates - DT] you will have to
decide if this gets the corrects results.

Note: A better name for [Dates - DT] would be StudentDetentions - it's
more descriptive, doesn't have spaces in it and someone would not have
to guess that "DT" meant detention.

The PARAMETERS clause identifies the data types of the data in the
controls on the form.

PARAMETERS Forms!ScanEntry!PassDateTime Date,
Forms!ScanEntry!PassnMaxUnExc Integer;
INSERT INTO DT ( SID, DTDateTime, ServeByDate, TotalLateCount )
SELECT DISTINCTROW P.SID, Forms!ScanEntry!PassDateTime AS Expr2,
D.ServeByDate, Forms!ScanEntry!PassnMaxUnExc AS Expr1
FROM Pass As P INNER JOIN [Dates - DT] AS D ON P.SID = D.SID
WHERE D.DTDate = Date()

In this case if you use the DISTINCTROW you don't need to use the GROUP
BY clause.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZ8DTIechKqOuFEgEQIpLwCg5+Uylfgoda0W7qGXIZlY9+HSeYkAoLUb
lm+uyrCmKM9JG+jzpz1bcrWc
=kP+k
-----END PGP SIGNATURE-----

Hi,

I'm having some troubles with a tardy database I'm creating, I created a
detention append query where I want to add all of the student tardies to
determine if they should receive a detention or not but it is not working.
when I run it, it request for me to enter a parameter value for Forms!
ScanEntry!PassSID, Forms!ScanEntry!PassDateTime, Forms!ScanEntry!
PassnMaxUnExc and then it says invalid use of Null.
The form is open and I ran the query, but I still get the Invalid use of Null
message. Where am I going wrong?


INSERT INTO DT ( SID, DTDateTime, ServeByDate, TotalLateCount )
SELECT DISTINCT Pass.SID, Max(Forms!ScanEntry!PassDateTime) AS Expr2, DLookUp
("[ServeByDate]","Dates - DT","[DTDate] = #" & Date() & "#") AS ServeByDate,
CInt(Forms!ScanEntry!PassnMaxUnExc) AS Expr1
FROM Pass
GROUP BY Pass.SID, DLookUp("[ServeByDate]","Dates - DT","[DTDate] = #" & Date
() & "#"), CInt(Forms!ScanEntry!PassnMaxUnExc);