Form not working - valid field name or expression?

[tezread]

I have a form where you enter a start and end date and a report is run. The source for the report is a cross tab query. This was working like a treat but now the error message says


the microsoft jet engne doesnt recgonise 'Date_referred' a a valid field name or expression.

see attached for illustration

enter start/end date and click patient seen by outcome 'preview report'

or embolic risk 'preview report'

 

[the_net_2.0]

this is the code that's behind that button:

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

    Dim stDocName As String

    stDocName = "Patients seen by outcome report"
    DoCmd.OpenReport stDocName, acPreview

Exit_Command20_Click:
    Exit Sub

Err_Command20_Click:
    MsgBox Err.Description
    Resume Exit_Command20_Click
    
End Sub

I'm getting a "printer driver isn't installed correctly" when I try it. It's acting like it's opening the report in normal mode. Could the report be corrupt, or maybe even the module?

 

[vbaInet]

Your database is pretty much like an Excel spreadsheet, i.e. a flat file. Not in any way relational. You really need to lookup the term normalization.

The problem you are experiencing is coming from the crosstab query itself and this can be traced to the fact that you have over 80 fields in your table. 2 tables only, no relationship?!?

You will certainly experience more problems in the future, no doubt.

To solve your problem, you need to use tblPatients as the source of the query, and not ViewAllData.

 

[tezread]

Your database is pretty much like an Excel spreadsheet, i.e. a flat file. Not in any way relational. You really need to lookup the term normalization.

The problem you are experiencing is coming from the crosstab query itself and this can be traced to the fact that you have over 80 fields in your table. 2 tables only, no relationship?!?

You will certainly experience more problems in the future, no doubt.

To solve your problem, you need to use tblPatients as the source of the query, and not ViewAllData.

I take your point about the lack of relations in the database. What I can do is have two tables. One called tblPatient, the other called tblTreatment. The aim being 1 patient could have one or more treatment episodes. That is all I can do to normalise this surely.

 

[vbaInet]

Do you mean what you can do to normalize? DCrake might be able to give you more precise on the stucture because he does alot of Healthcare databases but your database would (at least) have more than 3 tables (considering the amount of fields you have already). There are steps to follow in the normalization process and it's worth reading up. I could give you some links if you would like to read?

Did what I suggest before solve the problem?

 

[tezread]

Do you mean what you can do to normalize? DCrake might be able to give you more precise on the stucture because he does alot of Healthcare databases but your database would (at least) have more than 3 tables (considering the amount of fields you have already). There are steps to follow in the normalization process and it's worth reading up. I could give you some links if you would like to read?

Did what I suggest before solve the problem?

Been a while.. since I looked into theory of normalisation but I would welcome some links for sure.

I am sure I am limited though to creating just two tables. The table is big at the moment - yes - it covers clinical event, presenting symptoms, prescripitating factors, associated factors, past medical history, examination findings, initial findings, treatment options and discharge.

I did try your solution which works but I do have custom fields in the query viewalldata and I cannot reference them if I choose tblPatient as a source table

 

[vbaInet]

I tend to recommend these two:

http://support.microsoft.com/kb/304466
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Do you have to use all the fields in that query? I would doubt that's the case. My suggestion would be to only pull from that table the fields that you need for your query, perform the calculations in that and use that query.