Access 2003 Template Problem - Time and Billing

[davidelsbury]

Hi there


I've been trying to teach myself how to make Access 2003 code up a database
for time and billing - and the best template that I've found so far has been
the one that Microsoft ship with Access 2003, "Time and Billing". I have run
into the problem that the pictures below show, where when one comes to try
and generate an invoice, the fields display a "#Name?" error. As far as I can
tell, all the fields are populated correctly and there are no null values (I
even went so far as to create some zero dollar expenses to see if that fixed
the problem).

I would appreciate if someone could take a look at my database for me and
tell me what I am doing wrong. I did read another post on here from about 5
years ago where the advice given was similar, but for generating a report,
not using the functions I am using. I think it's to do with how Access is
using the "IIf" function, but that's as far as I have got.

Thanks in advance for any assistance or advice.

Cheers
David

Attachments:
(Client Window)
(Print Preview)
(Print Window)
http://www.easy-share.com/1916933068/DavidElsbury.mdb (Database)

[rpeare]

Create this query in your database and name it Qry_CurrentInvoice:

SELECT Sum([Time Card Hours].BillableHours) AS BilledHours, Sum([billablehours]*[billingrate]) AS BillAmt
FROM [Time Card Hours]
GROUP BY [Time Card Hours].DateWorked, [Time Card Hours].ProjectID
HAVING ((([Time Card Hours].DateWorked)>=[forms]![Print Invoice]![Begin Date] And ([Time Card Hours].DateWorked)<=[forms]![Print Invoice]![End Date]) AND (([Time Card Hours].ProjectID)=[forms]![Print Invoice]![Project ID]));

In the ON EXIT property of your start date and end date text boxes change your code to:

    Me![Print Invoice Time Subform].Requery
    Me![Print Invoice Expenses Subform].Requery
    If Not IsNull(Me![begin date]) And Not IsNull(Me![end date]) Then
        Me![total hours] = DLookup("[BilledHours]", "Qry_CurrentInVoice")
        Me![total hourly billings] = DLookup("[BillAmt]", "Qry_CurrentInVoice")
    End If

it's a bit brutish but it's quick and easy to make work, you'd do something similar for your expenses and likely on the report as well.

[ssanfu]

I downloaded your example MDB. I couldn't replicate your errors. Everything had values- no ERROR indicators.

Sometimes after making changes you have to close out of access then open it back up to see the changes.

[davidelsbury]

I downloaded your example MDB. I couldn't replicate your errors. Everything had values- no ERROR indicators.

Sometimes after making changes you have to close out of access then open it back up to see the changes.

Thanks to both of you for the replies. Ssanfu, I wonder if the reason you saw no errors is because you opened it in Access 2000? The title bar of my database says that it is "Access 2000 Format". I will see if I can get a copy of Access 2000 to try, it just seems a bit strange that Microsoft would send out an example database with errors in it.

Cheers
David

[davidelsbury]

Just posting a followup- thank you all for your help. I have got a copy of Access 2000 and the database works fine. No idea why there is a difference between 2000, and 2003 but it's a good enough work around for me.

Cheers
David