Access

[nbohana]

I am trying run this code. It ask me to supply the parameter? Well the
parameter is being passed by 'estselector', and is gotten from a dropdown
list. I check 'estselector' and it has the correct value. What am I doing
wrong. Please help thank you.

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String
Dim strFilter As String

strFilter = "[error-tracker].[estimator] = " & estselector

If IsNull(estselector) Then
MsgBox " *** You must Select a Bid. *** "
Else
DoCmd.OpenReport "ErrorEvalReport", acViewPreview, , strFilter
End If

Exit_Preview_Click:
Exit Sub

 

[John W. Vinson]

I am trying run this code. It ask me to supply the parameter? Well the
parameter is being passed by 'estselector', and is gotten from a dropdown
list. I check 'estselector' and it has the correct value. What am I doing
wrong. Please help thank you.

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String
Dim strFilter As String

strFilter = "[error-tracker].[estimator] = " & estselector

If IsNull(estselector) Then
MsgBox " *** You must Select a Bid. *** "
Else
DoCmd.OpenReport "ErrorEvalReport", acViewPreview, , strFilter
End If

Exit_Preview_Click:
Exit Sub

Is the field [estimator] of Text type? If so, you must include either ' or "
marks as delimiters: try

strFilter = "[error-tracker].[estimator] = '" & estselector & "'"

Exaggerated for clarity, that's

strFilter = "[error-tracker].[estimator] = ' " & estselector & " ' "


John W. Vinson [MVP]

 

[nbohana]

Hi John, thank you that solved the problem. I would like to know if you can
help me with another problem? The system that I am building has several
tables. The key is 'bid-number'. I use queries to access the data, and
example:

SELECT DISTINCT [error-tracker].[bid-number], [error-tracker].estimator,
[error-stats].[number-errors], [error-stats].[total-cost], sales.[job-name],
sales.tier, sales.[time-to-estimate], estimating.[estimating time],
sales.dprice, sales.dpricedate,
CCur([error-stats].[total-cost]+[error-stats].[total-cost]) AS TotalCost,
[error-tracker].[bid-suffix],
([error-stats].[number-errors]+[error-stats].[number-errors]) AS TotalErrors
FROM (([error-tracker] LEFT JOIN [error-stats] ON
[error-tracker].[bid-number] = [error-stats].[bid-number]) LEFT JOIN sales ON
[error-tracker].[bid-number] = sales.[bid-number]) LEFT JOIN estimating ON
[error-tracker].[bid-number] = estimating.[Bid-Number]
ORDER BY [error-tracker].[bid-number], [error-tracker].estimator,
[error-tracker].[bid-suffix];

What happens is I should only get 4 records at the most. What I am getting
is 4 records for each table, what Am I doing wrong. Hope youcan help!!
--
Norm Bohana

I am trying run this code. It ask me to supply the parameter? Well the
parameter is being passed by 'estselector', and is gotten from a dropdown
list. I check 'estselector' and it has the correct value. What am I doing
wrong. Please help thank you.

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String
Dim strFilter As String

strFilter = "[error-tracker].[estimator] = " & estselector

If IsNull(estselector) Then
MsgBox " *** You must Select a Bid. *** "
Else
DoCmd.OpenReport "ErrorEvalReport", acViewPreview, , strFilter
End If

Exit_Preview_Click:
Exit Sub

Is the field [estimator] of Text type? If so, you must include either ' or "
marks as delimiters: try

strFilter = "[error-tracker].[estimator] = '" & estselector & "'"

Exaggerated for clarity, that's

strFilter = "[error-tracker].[estimator] = ' " & estselector & " ' "


John W. Vinson [MVP]

 

[John W. Vinson]

Hi John, thank you that solved the problem. I would like to know if you can
help me with another problem? The system that I am building has several
tables. The key is 'bid-number'. I use queries to access the data, and
example:

SELECT DISTINCT [error-tracker].[bid-number], [error-tracker].estimator,
[error-stats].[number-errors], [error-stats].[total-cost], sales.[job-name],
sales.tier, sales.[time-to-estimate], estimating.[estimating time],
sales.dprice, sales.dpricedate,
CCur([error-stats].[total-cost]+[error-stats].[total-cost]) AS TotalCost,
[error-tracker].[bid-suffix],
([error-stats].[number-errors]+[error-stats].[number-errors]) AS TotalErrors
FROM (([error-tracker] LEFT JOIN [error-stats] ON
[error-tracker].[bid-number] = [error-stats].[bid-number]) LEFT JOIN sales ON
[error-tracker].[bid-number] = sales.[bid-number]) LEFT JOIN estimating ON
[error-tracker].[bid-number] = estimating.[Bid-Number]
ORDER BY [error-tracker].[bid-number], [error-tracker].estimator,
[error-tracker].[bid-suffix];

What happens is I should only get 4 records at the most. What I am getting
is 4 records for each table, what Am I doing wrong. Hope youcan help!!

Without knowing anything about the contents of the tables, all I can say is
that your JOINS will join every record in Error-Tracker with every record in
Error-Stats with every record in Sales with every record in Estimating, giving
every possible combination of records which have matches in all of those
tables; if there is no matching record in the LEFT JOINed tables, you'll get a
single record with NULLS in all fields for that table.

Could you post an example of the data in the tables, together with your
desired results and the actual results?

John W. Vinson [MVP]

 

[nbohana]

John how can I send you the information. Is there and email address that I
can use to send the info, or how can I get it to you.
--
Norm Bohana

Hi John, thank you that solved the problem. I would like to know if you can
help me with another problem? The system that I am building has several
tables. The key is 'bid-number'. I use queries to access the data, and
example:

SELECT DISTINCT [error-tracker].[bid-number], [error-tracker].estimator,
[error-stats].[number-errors], [error-stats].[total-cost], sales.[job-name],
sales.tier, sales.[time-to-estimate], estimating.[estimating time],
sales.dprice, sales.dpricedate,
CCur([error-stats].[total-cost]+[error-stats].[total-cost]) AS TotalCost,
[error-tracker].[bid-suffix],
([error-stats].[number-errors]+[error-stats].[number-errors]) AS TotalErrors
FROM (([error-tracker] LEFT JOIN [error-stats] ON
[error-tracker].[bid-number] = [error-stats].[bid-number]) LEFT JOIN sales ON
[error-tracker].[bid-number] = sales.[bid-number]) LEFT JOIN estimating ON
[error-tracker].[bid-number] = estimating.[Bid-Number]
ORDER BY [error-tracker].[bid-number], [error-tracker].estimator,
[error-tracker].[bid-suffix];

What happens is I should only get 4 records at the most. What I am getting
is 4 records for each table, what Am I doing wrong. Hope youcan help!!

Without knowing anything about the contents of the tables, all I can say is
that your JOINS will join every record in Error-Tracker with every record in
Error-Stats with every record in Sales with every record in Estimating, giving
every possible combination of records which have matches in all of those
tables; if there is no matching record in the LEFT JOINed tables, you'll get a
single record with NULLS in all fields for that table.

Could you post an example of the data in the tables, together with your
desired results and the actual results?

John W. Vinson [MVP]

 

[John W. Vinson]

John how can I send you the information. Is there and email address that I
can use to send the info, or how can I get it to you.

I'm sorry; as a self-employed consultant donating my time as a volunteer on
the newsgroups (like all the other folks who answer here), I must reserve
private EMail support to paying customers.

John W. Vinson [MVP]

 

[nbohana]

John I understand, I am also independent consultant. What are your fee's for
some support?

 

[John W. Vinson]

John I understand, I am also independent consultant. What are your fee's for
some support?

Email me offline at jvinson <at> wysard of info <dot> com - edit out the
blanks and the punctuation.

I'm fairly busy at present but might be able to squeeze in a small job.

John W. Vinson [MVP]