Can anyone tell me why the SQL query only works part of the time?

[LadyAmethyst]

Help Please?

I have a problem in Access 97. I have 1 form that is
supposed to display data from 2 records (tblCustomer &
tblJobSite). The problem is that on some records the
RecordSource ( an SQL query ) works fine and sometimes
it dosen't work at all and gives me a form with no data
in the form fields. (Yes there is data in the tables.)

There is 1 table that stores the Customer information.
There is 1 table that stores the Job Site information
(this allows for 1 Customer but many different Job Sites).
There is a print action that use information from both
the Customer and Job Site information and the current
form such as frmEstimate. There is a frmEstimate,
a frmRevisedEstimate, frmDeposit, frmWorkOrder etc.
All of these eventually lead to printing a document.
All of these forms have a command button that bring up
the screen with the Customer and Job Site information.
The frmJobSiteInformation has a command button that
allows the user to print the document needed.

If I open frmJobSiteInformation by itself (not through
the frmEstimate, then all the records are displayed.
Data Entry on this form (frmJobSiteInformation) is set
to "No" because this is not the place where data entry
or corrections need to be made. I have both a
frmCustomer goes to tblCustomer and frmJobSite goes to
tblJobSite this is the place to enter the initial
information. There is a CustomerID field in both
tblCustomer and tblJobSite. There is the JobID field in
both tblJobSite and tblEstimate et.al. Both JobID and
CustomerID are text fields.

The form is frmJobSiteInformation, the SQL query was
actually built by the forms Wizard.

SELECT DISTINCTROW tblJobSite.CustomerID, tblJobSite.JobID,
tblJobSite.ContactName, tblJobSite.JobSiteAddress,
tblJobSite.JobSiteCity, tblJobSite.JobSiteState,
tblJobSite.JobSitePhone, tblCustomer.CompanyName,
tblCustomer.CustContactName, tblCustomer.CustomerAddress,
tblCustomer.CustomerCity, tblCustomer.CustomerState,
tblCustomer.CustomerZipCode, tblCustomer.CustomerPhone
FROM tblCustomer INNER JOIN tblJobSite ON
tblCustomer.CustomerID = tblJobSite.CustomerID;

Now there is a command button in the frmEstimate
(cmdJobSiteInformation) that opens the frmJobSiteInformation
matching the field JobID in both the frmEstimate and
the frmJobSiteInformation.

Private Sub cmdJobSiteInformation_Click()
On Error GoTo Err_cmdJobSiteInformation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmJobSiteInformation"

stLinkCriteria = "[JobID]=" & "'" & Me![txtJobID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdJobSiteInformation_Click:
Exit Sub

Err_cmdJobSiteInformation_Click:
MsgBox Err.Description
Resume Exit_cmdJobSiteInformation_Click

End Sub

The frmJobSiteInformation is displayed to the screen
and it has a command button (cmdPrintEstimate)
after the Estimate is printed the user returns to the
frmEstimate and can exit back to the Switchboard
or enter another estimate.

I can't figure it out. Is there a better way that I
should be using? Please help, I'm at a dead end.

Thanks,

LadyAmethyst

 

[Michel Walsh]

Hi,


The SQL statement will return no record if there is no match, if there
is not a single tblJobSite.CustomerID for a given tblCustomer.CustomerID, or
vice-versa.


You need an outer join to return one of the two tables when there is no
match. Try with LEFT rather that INNER. tblCustomer.CustomerID will then
be always present, and if there is no match in tblJobSite.CustomerID, NULL
will be supplied.



Hoping it may help,
Vanderghast, Access MVP

Help Please?

I have a problem in Access 97. I have 1 form that is
supposed to display data from 2 records (tblCustomer &
tblJobSite). The problem is that on some records the
RecordSource ( an SQL query ) works fine and sometimes
it dosen't work at all and gives me a form with no data
in the form fields. (Yes there is data in the tables.)

There is 1 table that stores the Customer information.
There is 1 table that stores the Job Site information
(this allows for 1 Customer but many different Job Sites).
There is a print action that use information from both
the Customer and Job Site information and the current
form such as frmEstimate. There is a frmEstimate,
a frmRevisedEstimate, frmDeposit, frmWorkOrder etc.
All of these eventually lead to printing a document.
All of these forms have a command button that bring up
the screen with the Customer and Job Site information.
The frmJobSiteInformation has a command button that
allows the user to print the document needed.

If I open frmJobSiteInformation by itself (not through
the frmEstimate, then all the records are displayed.
Data Entry on this form (frmJobSiteInformation) is set
to "No" because this is not the place where data entry
or corrections need to be made. I have both a
frmCustomer goes to tblCustomer and frmJobSite goes to
tblJobSite this is the place to enter the initial
information. There is a CustomerID field in both
tblCustomer and tblJobSite. There is the JobID field in
both tblJobSite and tblEstimate et.al. Both JobID and
CustomerID are text fields.

The form is frmJobSiteInformation, the SQL query was
actually built by the forms Wizard.

SELECT DISTINCTROW tblJobSite.CustomerID, tblJobSite.JobID,
tblJobSite.ContactName, tblJobSite.JobSiteAddress,
tblJobSite.JobSiteCity, tblJobSite.JobSiteState,
tblJobSite.JobSitePhone, tblCustomer.CompanyName,
tblCustomer.CustContactName, tblCustomer.CustomerAddress,
tblCustomer.CustomerCity, tblCustomer.CustomerState,
tblCustomer.CustomerZipCode, tblCustomer.CustomerPhone
FROM tblCustomer INNER JOIN tblJobSite ON
tblCustomer.CustomerID = tblJobSite.CustomerID;

Now there is a command button in the frmEstimate
(cmdJobSiteInformation) that opens the frmJobSiteInformation
matching the field JobID in both the frmEstimate and
the frmJobSiteInformation.

Private Sub cmdJobSiteInformation_Click()
On Error GoTo Err_cmdJobSiteInformation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmJobSiteInformation"

stLinkCriteria = "[JobID]=" & "'" & Me![txtJobID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdJobSiteInformation_Click:
Exit Sub

Err_cmdJobSiteInformation_Click:
MsgBox Err.Description
Resume Exit_cmdJobSiteInformation_Click

End Sub

The frmJobSiteInformation is displayed to the screen
and it has a command button (cmdPrintEstimate)
after the Estimate is printed the user returns to the
frmEstimate and can exit back to the Switchboard
or enter another estimate.

I can't figure it out. Is there a better way that I
should be using? Please help, I'm at a dead end.

Thanks,

LadyAmethyst

 

[LadyAmethyst]

Michel,

I tried your answer and it still didn't work. So I got
frustrated and deleted the information from the
tblJobSite and re-entered it and now it works. I don't
know but the only thing I can figure out is that there
was maybe an invisible character in the information.

Thanks for you help.

LadyAmethyst

 

[Michel Walsh]

Hi,


I hope you didn't get frustrated against me...


It is possible that either a space, as you mentioned, either a 0 for a o, or
a 1 for a L, etc. Preferable to use a combo box and a "not it list" style,
if possible, to avoid those kinds of error difficult to spot.




Vanderghast, Access MVP

 

[LadyAmethyst]

Michel,

Are you kidding? No way frustated against you!! I never get frustrated
with the people who take the time to help me! Now, they may get frustradted
with me because I don't always understand the first time they try to explain
something, and I keep after them until I do!!

No, I just happened to remember about a problem that I had in Word that had
some kind of hidden character and wasn't acting like it was supposed to. So
I just figured that since Access is a "cousin" of Word the same thing might
be happening there too!

Thanks for your help.

LadyAmethyst