Parameter Query Form Problem

A

Addy

I have a crosstab query which is two joined tables giving a sum of
hours worked.

Now on my form I want to be able to select certain fields for a
criteria.

I want to search by

* Sold to Customer Name
* ContractStartDate (Greater than or equal to)
* ContractEndDate (Less than or equal to)
* ContractNumber

I want to search for all of these fields or even a single one. It is
not important that the user selects all for criterias. I have made a
form which has the following

* Combo Box for searching Customer Name
* Text Box for searching the start date
* Text Box for searching the End date
* Combo Box for searching Contract Number


I have pasted my code below. The problem is that when I run this query
directly WITHOUT the form, the logic works perfectly. however when I
run it via the form, it always gives blank records. It seems to me that
the form control values are not being passed to the query parameters
properly.

Here is my code.

PARAMETERS [Forms]![Transaction Details]![cbo_client] Text ( 255 ),
[Forms]![Transaction Details]![cbo_cont_num] Text ( 255 ),
[Forms]![Transaction Details]![txt_start_date] DateTime,
[Forms]![Transaction Details]![TXT_end_date] DateTime;
TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS [SumOfAdjusted
Hours]
SELECT [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy") AS
ActivityStart, [Hours Remaining].[Hours Used], [Hours
Remaining].HoursRemaining, [Hours Remaining].[Target Hours], Sum([Hours
Consumed].[Adjusted Hours]) AS [Adjusted Hours]
FROM [Hours Consumed] INNER JOIN [Hours Remaining] ON ([Hours
Consumed].[Sold to Customer Name]=[Hours Remaining].[Sold to Customer
Name]) AND ([Hours Consumed].ContractNumber=[Hours
Remaining].ContractNumber)
WHERE ((([Hours Consumed].[Sold to Customer
Name])=Forms![Transaction Details]!cbo_client Or Forms![Transaction
Details]!cbo_client Is Null) And (([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Or ([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Is Null) And (([Hours
Consumed].ContractEndDate)<=Forms![Transaction Details]!txt_End_date Or
([Hours Consumed].ContractEndDate)<=Forms![Transaction
Details]!txt_End_date Is Null) And (([Hours
Consumed].ContractNumber)=Forms![Transaction Details]!cbo_cont_num Or
Forms![Transaction Details]!cbo_cont_num Is Null))
GROUP BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), [Hours
Remaining].[Hours Used], [Hours Remaining].HoursRemaining, [Hours
Remaining].[Target Hours], [Hours Consumed].ContractStartDate, [Hours
Consumed].ContractEndDate
ORDER BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].[Service Order Number]
PIVOT [Hours Consumed].ActivityType;
 
A

Allen Browne

There is probably a timing issue here.

When the form first loads, Access has to run the query before it can assign
values to the controls on the form. But it needs the values from the
controls on the form so it can run the query. Catch 22. You might be able to
verify that this is the problem by issuing a Requery after it loads. If that
works, it is the chicken'n'egg problem.

A workaround might be to build the SQL statement for the form from the
unbound boxes that have a value. Say you add a command button for the user
to click after she enters the limiting values she wants. The Click event of
the command button would build the WHERE clause from the controls that have
a value, and patch it in between the stub of the SQL string (TRANSFORM and
SELECT clauses) and the tail (ORDER BY and PIVOT clauses.)

This kind of thing (comletely untested aircode--concept only):

Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

Const strcStub = "TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS
[SumOfAdjusted Hours] " & _
"SELECT [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],""dddd', 'mmm d yyyy"") AS ActivityStart, " & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" Sum([Hours Consumed].[Adjusted Hours]) AS [Adjusted Hours] " & vbCrLf & _
"FROM [Hours Consumed] INNER JOIN [Hours Remaining] " & _
" ON ([Hours Consumed].[Sold to Customer Name] " & _
" =[Hours Remaining].[Sold to Customer Name]) " & _
" AND ([Hours Consumed].ContractNumber=[Hours Remaining].ContractNumber) "

Const strcTail = "GROUP BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), " & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate " & vbCrLf & _
"ORDER BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].[Service Order Number] " & vbCrLf & _
"PIVOT [Hours Consumed].ActivityType; " & _

'Build the WHERE clause from the unbound controls with a value.
If Not IsNull(Me.cbo_client) Then
strWhere = "([Hours Consumed].[Sold to Customer Name] = """ & _
Me.cbo_client & """) AND "
End If
If Not IsNull(Me.txt_start_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If
If Not IsNull(Me.txt_end_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate < " & _
Format(Me.txt_end_date + 1, strcJetDate) & ") AND "
End If
If Not IsNull(Me.cbo_cont_num) Then
strWhere = strWhere & "([Hours Consumed].ContractNumber = """ & _
Me.cbo_cont_num & """) AND "
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strWhere = " WHERE " & Left$(strWhere, lngLen)
End If

'Finally: assign the whole thing as the RecordSource of the form.
Me.RecordSource = strcStub & strWhere & strcTail

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
I have a crosstab query which is two joined tables giving a sum of
hours worked.

Now on my form I want to be able to select certain fields for a
criteria.

I want to search by

* Sold to Customer Name
* ContractStartDate (Greater than or equal to)
* ContractEndDate (Less than or equal to)
* ContractNumber

I want to search for all of these fields or even a single one. It is
not important that the user selects all for criterias. I have made a
form which has the following

* Combo Box for searching Customer Name
* Text Box for searching the start date
* Text Box for searching the End date
* Combo Box for searching Contract Number


I have pasted my code below. The problem is that when I run this query
directly WITHOUT the form, the logic works perfectly. however when I
run it via the form, it always gives blank records. It seems to me that
the form control values are not being passed to the query parameters
properly.

Here is my code.

PARAMETERS [Forms]![Transaction Details]![cbo_client] Text ( 255 ),
[Forms]![Transaction Details]![cbo_cont_num] Text ( 255 ),
[Forms]![Transaction Details]![txt_start_date] DateTime,
[Forms]![Transaction Details]![TXT_end_date] DateTime;
TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS [SumOfAdjusted
Hours]
SELECT [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy") AS
ActivityStart, [Hours Remaining].[Hours Used], [Hours
Remaining].HoursRemaining, [Hours Remaining].[Target Hours], Sum([Hours
Consumed].[Adjusted Hours]) AS [Adjusted Hours]
FROM [Hours Consumed] INNER JOIN [Hours Remaining] ON ([Hours
Consumed].[Sold to Customer Name]=[Hours Remaining].[Sold to Customer
Name]) AND ([Hours Consumed].ContractNumber=[Hours
Remaining].ContractNumber)
WHERE ((([Hours Consumed].[Sold to Customer
Name])=Forms![Transaction Details]!cbo_client Or Forms![Transaction
Details]!cbo_client Is Null) And (([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Or ([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Is Null) And (([Hours
Consumed].ContractEndDate)<=Forms![Transaction Details]!txt_End_date Or
([Hours Consumed].ContractEndDate)<=Forms![Transaction
Details]!txt_End_date Is Null) And (([Hours
Consumed].ContractNumber)=Forms![Transaction Details]!cbo_cont_num Or
Forms![Transaction Details]!cbo_cont_num Is Null))
GROUP BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), [Hours
Remaining].[Hours Used], [Hours Remaining].HoursRemaining, [Hours
Remaining].[Target Hours], [Hours Consumed].ContractStartDate, [Hours
Consumed].ContractEndDate
ORDER BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].[Service Order Number]
PIVOT [Hours Consumed].ActivityType;
Click to expand...
 
A

Addy

Thanks Allen, I shall try you method and let you know the results.

This is what I used to make my query, its was a Microsoft support site.

http://support.microsoft.com/kb/304428/EN-US/


Allen said:
There is probably a timing issue here.

When the form first loads, Access has to run the query before it can assign
values to the controls on the form. But it needs the values from the
controls on the form so it can run the query. Catch 22. You might be able to
verify that this is the problem by issuing a Requery after it loads. If that
works, it is the chicken'n'egg problem.

A workaround might be to build the SQL statement for the form from the
unbound boxes that have a value. Say you add a command button for the user
to click after she enters the limiting values she wants. The Click event of
the command button would build the WHERE clause from the controls that have
a value, and patch it in between the stub of the SQL string (TRANSFORM and
SELECT clauses) and the tail (ORDER BY and PIVOT clauses.)

This kind of thing (comletely untested aircode--concept only):

Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

Const strcStub = "TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS
[SumOfAdjusted Hours] " & _
"SELECT [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],""dddd', 'mmm d yyyy"") AS ActivityStart, " & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" Sum([Hours Consumed].[Adjusted Hours]) AS [Adjusted Hours] " & vbCrLf & _
"FROM [Hours Consumed] INNER JOIN [Hours Remaining] " & _
" ON ([Hours Consumed].[Sold to Customer Name] " & _
" =[Hours Remaining].[Sold to Customer Name]) " & _
" AND ([Hours Consumed].ContractNumber=[Hours Remaining].ContractNumber) "

Const strcTail = "GROUP BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), " & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate " & vbCrLf & _
"ORDER BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].[Service Order Number] " & vbCrLf & _
"PIVOT [Hours Consumed].ActivityType; " & _

'Build the WHERE clause from the unbound controls with a value.
If Not IsNull(Me.cbo_client) Then
strWhere = "([Hours Consumed].[Sold to Customer Name] = """ & _
Me.cbo_client & """) AND "
End If
If Not IsNull(Me.txt_start_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If
If Not IsNull(Me.txt_end_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate < " & _
Format(Me.txt_end_date + 1, strcJetDate) & ") AND "
End If
If Not IsNull(Me.cbo_cont_num) Then
strWhere = strWhere & "([Hours Consumed].ContractNumber = """ & _
Me.cbo_cont_num & """) AND "
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strWhere = " WHERE " & Left$(strWhere, lngLen)
End If

'Finally: assign the whole thing as the RecordSource of the form.
Me.RecordSource = strcStub & strWhere & strcTail

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
I have a crosstab query which is two joined tables giving a sum of
hours worked.

Now on my form I want to be able to select certain fields for a
criteria.

I want to search by

* Sold to Customer Name
* ContractStartDate (Greater than or equal to)
* ContractEndDate (Less than or equal to)
* ContractNumber

I want to search for all of these fields or even a single one. It is
not important that the user selects all for criterias. I have made a
form which has the following

* Combo Box for searching Customer Name
* Text Box for searching the start date
* Text Box for searching the End date
* Combo Box for searching Contract Number


I have pasted my code below. The problem is that when I run this query
directly WITHOUT the form, the logic works perfectly. however when I
run it via the form, it always gives blank records. It seems to me that
the form control values are not being passed to the query parameters
properly.

Here is my code.

PARAMETERS [Forms]![Transaction Details]![cbo_client] Text ( 255 ),
[Forms]![Transaction Details]![cbo_cont_num] Text ( 255 ),
[Forms]![Transaction Details]![txt_start_date] DateTime,
[Forms]![Transaction Details]![TXT_end_date] DateTime;
TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS [SumOfAdjusted
Hours]
SELECT [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy") AS
ActivityStart, [Hours Remaining].[Hours Used], [Hours
Remaining].HoursRemaining, [Hours Remaining].[Target Hours], Sum([Hours
Consumed].[Adjusted Hours]) AS [Adjusted Hours]
FROM [Hours Consumed] INNER JOIN [Hours Remaining] ON ([Hours
Consumed].[Sold to Customer Name]=[Hours Remaining].[Sold to Customer
Name]) AND ([Hours Consumed].ContractNumber=[Hours
Remaining].ContractNumber)
WHERE ((([Hours Consumed].[Sold to Customer
Name])=Forms![Transaction Details]!cbo_client Or Forms![Transaction
Details]!cbo_client Is Null) And (([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Or ([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Is Null) And (([Hours
Consumed].ContractEndDate)<=Forms![Transaction Details]!txt_End_date Or
([Hours Consumed].ContractEndDate)<=Forms![Transaction
Details]!txt_End_date Is Null) And (([Hours
Consumed].ContractNumber)=Forms![Transaction Details]!cbo_cont_num Or
Forms![Transaction Details]!cbo_cont_num Is Null))
GROUP BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), [Hours
Remaining].[Hours Used], [Hours Remaining].HoursRemaining, [Hours
Remaining].[Target Hours], [Hours Consumed].ContractStartDate, [Hours
Consumed].ContractEndDate
ORDER BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].[Service Order Number]
PIVOT [Hours Consumed].ActivityType;
Click to expand...
Click to expand...
 
A

Addy

How would I requery it.

Here is my button code.

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Dim stDocName As String

stDocName = "Trial"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.Requery
Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub

Thanks Allen, I shall try you method and let you know the results.

This is what I used to make my query, its was a Microsoft support site.

http://support.microsoft.com/kb/304428/EN-US/


Allen said:
There is probably a timing issue here.

When the form first loads, Access has to run the query before it can assign
values to the controls on the form. But it needs the values from the
controls on the form so it can run the query. Catch 22. You might be able to
verify that this is the problem by issuing a Requery after it loads. If that
works, it is the chicken'n'egg problem.

A workaround might be to build the SQL statement for the form from the
unbound boxes that have a value. Say you add a command button for the user
to click after she enters the limiting values she wants. The Click event of
the command button would build the WHERE clause from the controls that have
a value, and patch it in between the stub of the SQL string (TRANSFORM and
SELECT clauses) and the tail (ORDER BY and PIVOT clauses.)

This kind of thing (comletely untested aircode--concept only):

Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

Const strcStub = "TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS
[SumOfAdjusted Hours] " & _
"SELECT [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],""dddd', 'mmm d yyyy"") AS ActivityStart, " & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" Sum([Hours Consumed].[Adjusted Hours]) AS [Adjusted Hours] " & vbCrLf & _
"FROM [Hours Consumed] INNER JOIN [Hours Remaining] " & _
" ON ([Hours Consumed].[Sold to Customer Name] " & _
" =[Hours Remaining].[Sold to Customer Name]) " & _
" AND ([Hours Consumed].ContractNumber=[Hours Remaining].ContractNumber) "

Const strcTail = "GROUP BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), " & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate " & vbCrLf & _
"ORDER BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].[Service Order Number] " & vbCrLf & _
"PIVOT [Hours Consumed].ActivityType; " & _

'Build the WHERE clause from the unbound controls with a value.
If Not IsNull(Me.cbo_client) Then
strWhere = "([Hours Consumed].[Sold to Customer Name] = """ & _
Me.cbo_client & """) AND "
End If
If Not IsNull(Me.txt_start_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If
If Not IsNull(Me.txt_end_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate < " & _
Format(Me.txt_end_date + 1, strcJetDate) & ") AND "
End If
If Not IsNull(Me.cbo_cont_num) Then
strWhere = strWhere & "([Hours Consumed].ContractNumber = """ & _
Me.cbo_cont_num & """) AND "
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strWhere = " WHERE " & Left$(strWhere, lngLen)
End If

'Finally: assign the whole thing as the RecordSource of the form.
Me.RecordSource = strcStub & strWhere & strcTail

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
I have a crosstab query which is two joined tables giving a sum of
hours worked.

Now on my form I want to be able to select certain fields for a
criteria.

I want to search by

* Sold to Customer Name
* ContractStartDate (Greater than or equal to)
* ContractEndDate (Less than or equal to)
* ContractNumber

I want to search for all of these fields or even a single one. It is
not important that the user selects all for criterias. I have made a
form which has the following

* Combo Box for searching Customer Name
* Text Box for searching the start date
* Text Box for searching the End date
* Combo Box for searching Contract Number


I have pasted my code below. The problem is that when I run this query
directly WITHOUT the form, the logic works perfectly. however when I
run it via the form, it always gives blank records. It seems to me that
the form control values are not being passed to the query parameters
properly.

Here is my code.

PARAMETERS [Forms]![Transaction Details]![cbo_client] Text ( 255 ),
[Forms]![Transaction Details]![cbo_cont_num] Text ( 255 ),
[Forms]![Transaction Details]![txt_start_date] DateTime,
[Forms]![Transaction Details]![TXT_end_date] DateTime;
TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS [SumOfAdjusted
Hours]
SELECT [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy") AS
ActivityStart, [Hours Remaining].[Hours Used], [Hours
Remaining].HoursRemaining, [Hours Remaining].[Target Hours], Sum([Hours
Consumed].[Adjusted Hours]) AS [Adjusted Hours]
FROM [Hours Consumed] INNER JOIN [Hours Remaining] ON ([Hours
Consumed].[Sold to Customer Name]=[Hours Remaining].[Sold to Customer
Name]) AND ([Hours Consumed].ContractNumber=[Hours
Remaining].ContractNumber)
WHERE ((([Hours Consumed].[Sold to Customer
Name])=Forms![Transaction Details]!cbo_client Or Forms![Transaction
Details]!cbo_client Is Null) And (([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Or ([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Is Null) And (([Hours
Consumed].ContractEndDate)<=Forms![Transaction Details]!txt_End_date Or
([Hours Consumed].ContractEndDate)<=Forms![Transaction
Details]!txt_End_date Is Null) And (([Hours
Consumed].ContractNumber)=Forms![Transaction Details]!cbo_cont_num Or
Forms![Transaction Details]!cbo_cont_num Is Null))
GROUP BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), [Hours
Remaining].[Hours Used], [Hours Remaining].HoursRemaining, [Hours
Remaining].[Target Hours], [Hours Consumed].ContractStartDate, [Hours
Consumed].ContractEndDate
ORDER BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].[Service Order Number]
PIVOT [Hours Consumed].ActivityType;
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

From the keyboard: F9 (or was that Shift+F9).

In code:
Forms!form1.Requery

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
How would I requery it.

Here is my button code.

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Dim stDocName As String

stDocName = "Trial"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.Requery
Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub

Thanks Allen, I shall try you method and let you know the results.

This is what I used to make my query, its was a Microsoft support site.

http://support.microsoft.com/kb/304428/EN-US/


Allen said:
There is probably a timing issue here.

When the form first loads, Access has to run the query before it can
assign
values to the controls on the form. But it needs the values from the
controls on the form so it can run the query. Catch 22. You might be
able to
verify that this is the problem by issuing a Requery after it loads. If
that
works, it is the chicken'n'egg problem.

A workaround might be to build the SQL statement for the form from the
unbound boxes that have a value. Say you add a command button for the
user
to click after she enters the limiting values she wants. The Click
event of
the command button would build the WHERE clause from the controls that
have
a value, and patch it in between the stub of the SQL string (TRANSFORM
and
SELECT clauses) and the tail (ORDER BY and PIVOT clauses.)

This kind of thing (comletely untested aircode--concept only):

Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

Const strcStub = "TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS
[SumOfAdjusted Hours] " & _
"SELECT [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],""dddd', 'mmm d yyyy"") AS ActivityStart,
" & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" Sum([Hours Consumed].[Adjusted Hours]) AS [Adjusted Hours] " & vbCrLf
& _
"FROM [Hours Consumed] INNER JOIN [Hours Remaining] " & _
" ON ([Hours Consumed].[Sold to Customer Name] " & _
" =[Hours Remaining].[Sold to Customer Name]) " & _
" AND ([Hours Consumed].ContractNumber=[Hours
Remaining].ContractNumber) "

Const strcTail = "GROUP BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), " & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate " & vbCrLf & _
"ORDER BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].[Service Order Number] " & vbCrLf & _
"PIVOT [Hours Consumed].ActivityType; " & _

'Build the WHERE clause from the unbound controls with a value.
If Not IsNull(Me.cbo_client) Then
strWhere = "([Hours Consumed].[Sold to Customer Name] = """ & _
Me.cbo_client & """) AND "
End If
If Not IsNull(Me.txt_start_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If
If Not IsNull(Me.txt_end_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate < " & _
Format(Me.txt_end_date + 1, strcJetDate) & ") AND "
End If
If Not IsNull(Me.cbo_cont_num) Then
strWhere = strWhere & "([Hours Consumed].ContractNumber = """ & _
Me.cbo_cont_num & """) AND "
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strWhere = " WHERE " & Left$(strWhere, lngLen)
End If

'Finally: assign the whole thing as the RecordSource of the form.
Me.RecordSource = strcStub & strWhere & strcTail

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a crosstab query which is two joined tables giving a sum of
hours worked.

Now on my form I want to be able to select certain fields for a
criteria.

I want to search by

* Sold to Customer Name
* ContractStartDate (Greater than or equal to)
* ContractEndDate (Less than or equal to)
* ContractNumber

I want to search for all of these fields or even a single one. It is
not important that the user selects all for criterias. I have made a
form which has the following

* Combo Box for searching Customer Name
* Text Box for searching the start date
* Text Box for searching the End date
* Combo Box for searching Contract Number


I have pasted my code below. The problem is that when I run this
query
directly WITHOUT the form, the logic works perfectly. however when I
run it via the form, it always gives blank records. It seems to me
that
the form control values are not being passed to the query parameters
properly.

Here is my code.

PARAMETERS [Forms]![Transaction Details]![cbo_client] Text ( 255 ),
[Forms]![Transaction Details]![cbo_cont_num] Text ( 255 ),
[Forms]![Transaction Details]![txt_start_date] DateTime,
[Forms]![Transaction Details]![TXT_end_date] DateTime;
TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS [SumOfAdjusted
Hours]
SELECT [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy") AS
ActivityStart, [Hours Remaining].[Hours Used], [Hours
Remaining].HoursRemaining, [Hours Remaining].[Target Hours],
Sum([Hours
Consumed].[Adjusted Hours]) AS [Adjusted Hours]
FROM [Hours Consumed] INNER JOIN [Hours Remaining] ON ([Hours
Consumed].[Sold to Customer Name]=[Hours Remaining].[Sold to Customer
Name]) AND ([Hours Consumed].ContractNumber=[Hours
Remaining].ContractNumber)
WHERE ((([Hours Consumed].[Sold to Customer
Name])=Forms![Transaction Details]!cbo_client Or Forms![Transaction
Details]!cbo_client Is Null) And (([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Or ([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Is Null) And (([Hours
Consumed].ContractEndDate)<=Forms![Transaction Details]!txt_End_date
Or
([Hours Consumed].ContractEndDate)<=Forms![Transaction
Details]!txt_End_date Is Null) And (([Hours
Consumed].ContractNumber)=Forms![Transaction Details]!cbo_cont_num Or
Forms![Transaction Details]!cbo_cont_num Is Null))
GROUP BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), [Hours
Remaining].[Hours Used], [Hours Remaining].HoursRemaining, [Hours
Remaining].[Target Hours], [Hours Consumed].ContractStartDate, [Hours
Consumed].ContractEndDate
ORDER BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].[Service Order Number]
PIVOT [Hours Consumed].ActivityType;
Click to expand...
Click to expand...
Click to expand...
 
A

Addy

No this did not work, I shall try it with code. I did the code approach
the last time I worked with access which was 4 years ago.
Allen said:
From the keyboard: F9 (or was that Shift+F9).

In code:
Forms!form1.Requery

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
How would I requery it.

Here is my button code.

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Dim stDocName As String

stDocName = "Trial"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.Requery
Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub

Thanks Allen, I shall try you method and let you know the results.

This is what I used to make my query, its was a Microsoft support site.

http://support.microsoft.com/kb/304428/EN-US/


Allen Browne wrote:
There is probably a timing issue here.

When the form first loads, Access has to run the query before it can
assign
values to the controls on the form. But it needs the values from the
controls on the form so it can run the query. Catch 22. You might be
able to
verify that this is the problem by issuing a Requery after it loads. If
that
works, it is the chicken'n'egg problem.

A workaround might be to build the SQL statement for the form from the
unbound boxes that have a value. Say you add a command button for the
user
to click after she enters the limiting values she wants. The Click
event of
the command button would build the WHERE clause from the controls that
have
a value, and patch it in between the stub of the SQL string (TRANSFORM
and
SELECT clauses) and the tail (ORDER BY and PIVOT clauses.)

This kind of thing (comletely untested aircode--concept only):

Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

Const strcStub = "TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS
[SumOfAdjusted Hours] " & _
"SELECT [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],""dddd', 'mmm d yyyy"") AS ActivityStart,
" & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" Sum([Hours Consumed].[Adjusted Hours]) AS [Adjusted Hours] " & vbCrLf
& _
"FROM [Hours Consumed] INNER JOIN [Hours Remaining] " & _
" ON ([Hours Consumed].[Sold to Customer Name] " & _
" =[Hours Remaining].[Sold to Customer Name]) " & _
" AND ([Hours Consumed].ContractNumber=[Hours
Remaining].ContractNumber) "

Const strcTail = "GROUP BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate, " & _
" [Hours Consumed].[Service Order Number], " & _
" [Hours Consumed].[Sold to Customer Name], " & _
" Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), " & _
" [Hours Remaining].[Hours Used], " & _
" [Hours Remaining].HoursRemaining, " & _
" [Hours Remaining].[Target Hours], " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].ContractEndDate " & vbCrLf & _
"ORDER BY [Hours Consumed].ContractNumber, " & _
" [Hours Consumed].ContractStartDate, " & _
" [Hours Consumed].[Service Order Number] " & vbCrLf & _
"PIVOT [Hours Consumed].ActivityType; " & _

'Build the WHERE clause from the unbound controls with a value.
If Not IsNull(Me.cbo_client) Then
strWhere = "([Hours Consumed].[Sold to Customer Name] = """ & _
Me.cbo_client & """) AND "
End If
If Not IsNull(Me.txt_start_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If
If Not IsNull(Me.txt_end_date) Then
strWhere = strWhere & "([Hours Consumed].ContractStartDate < " & _
Format(Me.txt_end_date + 1, strcJetDate) & ") AND "
End If
If Not IsNull(Me.cbo_cont_num) Then
strWhere = strWhere & "([Hours Consumed].ContractNumber = """ & _
Me.cbo_cont_num & """) AND "
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strWhere = " WHERE " & Left$(strWhere, lngLen)
End If

'Finally: assign the whole thing as the RecordSource of the form.
Me.RecordSource = strcStub & strWhere & strcTail

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a crosstab query which is two joined tables giving a sum of
hours worked.

Now on my form I want to be able to select certain fields for a
criteria.

I want to search by

* Sold to Customer Name
* ContractStartDate (Greater than or equal to)
* ContractEndDate (Less than or equal to)
* ContractNumber

I want to search for all of these fields or even a single one. It is
not important that the user selects all for criterias. I have made a
form which has the following

* Combo Box for searching Customer Name
* Text Box for searching the start date
* Text Box for searching the End date
* Combo Box for searching Contract Number


I have pasted my code below. The problem is that when I run this
query
directly WITHOUT the form, the logic works perfectly. however when I
run it via the form, it always gives blank records. It seems to me
that
the form control values are not being passed to the query parameters
properly.

Here is my code.

PARAMETERS [Forms]![Transaction Details]![cbo_client] Text ( 255 ),
[Forms]![Transaction Details]![cbo_cont_num] Text ( 255 ),
[Forms]![Transaction Details]![txt_start_date] DateTime,
[Forms]![Transaction Details]![TXT_end_date] DateTime;
TRANSFORM Sum([Hours Consumed].[Adjusted Hours]) AS [SumOfAdjusted
Hours]
SELECT [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy") AS
ActivityStart, [Hours Remaining].[Hours Used], [Hours
Remaining].HoursRemaining, [Hours Remaining].[Target Hours],
Sum([Hours
Consumed].[Adjusted Hours]) AS [Adjusted Hours]
FROM [Hours Consumed] INNER JOIN [Hours Remaining] ON ([Hours
Consumed].[Sold to Customer Name]=[Hours Remaining].[Sold to Customer
Name]) AND ([Hours Consumed].ContractNumber=[Hours
Remaining].ContractNumber)
WHERE ((([Hours Consumed].[Sold to Customer
Name])=Forms![Transaction Details]!cbo_client Or Forms![Transaction
Details]!cbo_client Is Null) And (([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Or ([Hours
Consumed].ContractStartDate)>=Forms![Transaction
Details]!txt_start_date Is Null) And (([Hours
Consumed].ContractEndDate)<=Forms![Transaction Details]!txt_End_date
Or
([Hours Consumed].ContractEndDate)<=Forms![Transaction
Details]!txt_End_date Is Null) And (([Hours
Consumed].ContractNumber)=Forms![Transaction Details]!cbo_cont_num Or
Forms![Transaction Details]!cbo_cont_num Is Null))
GROUP BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].ContractEndDate, [Hours
Consumed].[Service Order Number], [Hours Consumed].[Sold to Customer
Name], Format([ActivityStartDate],"dddd"", ""mmm d yyyy"), [Hours
Remaining].[Hours Used], [Hours Remaining].HoursRemaining, [Hours
Remaining].[Target Hours], [Hours Consumed].ContractStartDate, [Hours
Consumed].ContractEndDate
ORDER BY [Hours Consumed].ContractNumber, [Hours
Consumed].ContractStartDate, [Hours Consumed].[Service Order Number]
PIVOT [Hours Consumed].ActivityType;
Click to expand...
Click to expand...
Click to expand...
 
A

Addy

Here is the code I came up with.

Private Sub Command16_Click()



strSQLStart = "TRANSFORM Sum([Hours Consumed].[Adjusted Hours])
AS [SumOfAdjusted Hours]SELECT [Hours Consumed].ContractNumber"
strSQLStart = strSQLStart & " , [Hours
Consumed].ContractEndDate"
strSQLStart = strSQLStart & " , [Hours
Consumed].ContractStartDate"
strSQLStart = strSQLStart & " , [Hours Consumed].[Service Order
Number]"
strSQLStart = strSQLStart & " , [Hours Consumed].[Sold to
Customer Name]"
strSQLStart = strSQLStart & " ,
Format([ActivityStartDate],""dddd"""", """"mmm d yyyy"") AS
ActivityStart"
strSQLStart = strSQLStart & " , [Hours Remaining].[Hours Used]"
strSQLStart = strSQLStart & " , [Hours
Remaining].HoursRemaining"
strSQLStart = strSQLStart & " , [Hours Remaining].[Target
Hours]"
strSQLStart = strSQLStart & " , Sum([Hours Consumed].[Adjusted
Hours]) AS [Adjusted Hours]"
strSQLFrom = " From [Hours Consumed] "
strSQLFrom = strSQLFrom & " INNER JOIN [Hours Remaining] "
strSQLFrom = strSQLFrom & " ON ([Hours Consumed].[Sold to
Customer Name] = [Hours Remaining].[Sold to Customer Name]) "
strSQLFrom = strSQLFrom & " AND ([Hours Consumed].ContractNumber
= [Hours Remaining].ContractNumber)"
strSQLFrom = strSQLFrom & " GROUP BY [Hours
Consumed].ContractNumber"
strSQLFrom = strSQLFrom & " , [Hours Consumed].ContractEndDate"
strSQLFrom = strSQLFrom & " , [Hours
Consumed].ContractStartDate"
strSQLFrom = strSQLFrom & " , [Hours Consumed].[Service Order
Number]"
strSQLFrom = strSQLFrom & " , [Hours Consumed].[Sold to Customer
Name]"
strSQLFrom = strSQLFrom & " ,
Format([ActivityStartDate],""dddd"""", """"mmm d yyyy"")"
strSQLFrom = strSQLFrom & " , [Hours Remaining].[Hours Used]"
strSQLFrom = strSQLFrom & " , [Hours Remaining].HoursRemaining"
strSQLFrom = strSQLFrom & " , [Hours Remaining].[Target Hours]"
strSQLOrder = " Order By [Hours Consumed].ContractNumber"
strSQLOrder = strSQLOrder & " , [Hours Consumed].[Service Order
Number]PIVOT [Hours Consumed].ActivityType;"



If Not IsNull(Me.cbo_client) Then
strSQLWhere = "([Hours Consumed].[Sold to Customer Name] = """
& _
Me.cbo_client & """) AND "
End If

If Not IsNull(Me.txt_start_date) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractStartDate
= " & _
Click to expand...
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If

If Not IsNull(Me.TXT_end_date) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractStartDate <
" & _
Format(Me.TXT_end_date + 1, strcJetDate) & ") AND "
End If

If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractNumber =
""" & _
Me.cbo_cont_num & """) AND "
End If

lngLen = Len(strSQLWhere) - 5 'Without trailing " AND "

If lngLen > 0 Then
strSQLWhere = " WHERE " & Left$(strSQLWhere, lngLen)
End If


strSQL = strSQLStart & strSQLFrom & strSQLWhere & strSQLOrder

Me.Form.RecordSource = strSQL


End Sub

Shall I invoke with the click button or on the form load? Please
verify. In the end I want this to open a report which is based on a
query.
 
A

Allen Browne

Hang on: do you want to see this in the form, or are you trying to output
this to a report?

The code you have is suited for the form, and should work when you click the
button.

If you want to then pass it to a report, you could put similar code in the
report's Open event (referring to the controls on the form instead of Me, of
course.) Or, in Access 2002 or 2003 you could pass the entire string from
the form to the report in the OpenArgs argument of OpenReport, and then use
Report_Open to apply it to the report's RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
Here is the code I came up with.

Private Sub Command16_Click()



strSQLStart = "TRANSFORM Sum([Hours Consumed].[Adjusted Hours])
AS [SumOfAdjusted Hours]SELECT [Hours Consumed].ContractNumber"
strSQLStart = strSQLStart & " , [Hours
Consumed].ContractEndDate"
strSQLStart = strSQLStart & " , [Hours
Consumed].ContractStartDate"
strSQLStart = strSQLStart & " , [Hours Consumed].[Service Order
Number]"
strSQLStart = strSQLStart & " , [Hours Consumed].[Sold to
Customer Name]"
strSQLStart = strSQLStart & " ,
Format([ActivityStartDate],""dddd"""", """"mmm d yyyy"") AS
ActivityStart"
strSQLStart = strSQLStart & " , [Hours Remaining].[Hours Used]"
strSQLStart = strSQLStart & " , [Hours
Remaining].HoursRemaining"
strSQLStart = strSQLStart & " , [Hours Remaining].[Target
Hours]"
strSQLStart = strSQLStart & " , Sum([Hours Consumed].[Adjusted
Hours]) AS [Adjusted Hours]"
strSQLFrom = " From [Hours Consumed] "
strSQLFrom = strSQLFrom & " INNER JOIN [Hours Remaining] "
strSQLFrom = strSQLFrom & " ON ([Hours Consumed].[Sold to
Customer Name] = [Hours Remaining].[Sold to Customer Name]) "
strSQLFrom = strSQLFrom & " AND ([Hours Consumed].ContractNumber
= [Hours Remaining].ContractNumber)"
strSQLFrom = strSQLFrom & " GROUP BY [Hours
Consumed].ContractNumber"
strSQLFrom = strSQLFrom & " , [Hours Consumed].ContractEndDate"
strSQLFrom = strSQLFrom & " , [Hours
Consumed].ContractStartDate"
strSQLFrom = strSQLFrom & " , [Hours Consumed].[Service Order
Number]"
strSQLFrom = strSQLFrom & " , [Hours Consumed].[Sold to Customer
Name]"
strSQLFrom = strSQLFrom & " ,
Format([ActivityStartDate],""dddd"""", """"mmm d yyyy"")"
strSQLFrom = strSQLFrom & " , [Hours Remaining].[Hours Used]"
strSQLFrom = strSQLFrom & " , [Hours Remaining].HoursRemaining"
strSQLFrom = strSQLFrom & " , [Hours Remaining].[Target Hours]"
strSQLOrder = " Order By [Hours Consumed].ContractNumber"
strSQLOrder = strSQLOrder & " , [Hours Consumed].[Service Order
Number]PIVOT [Hours Consumed].ActivityType;"



If Not IsNull(Me.cbo_client) Then
strSQLWhere = "([Hours Consumed].[Sold to Customer Name] = """
& _
Me.cbo_client & """) AND "
End If

If Not IsNull(Me.txt_start_date) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractStartDate
= " & _
Click to expand...
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If

If Not IsNull(Me.TXT_end_date) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractStartDate <
" & _
Format(Me.TXT_end_date + 1, strcJetDate) & ") AND "
End If

If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractNumber =
""" & _
Me.cbo_cont_num & """) AND "
End If

lngLen = Len(strSQLWhere) - 5 'Without trailing " AND "

If lngLen > 0 Then
strSQLWhere = " WHERE " & Left$(strSQLWhere, lngLen)
End If


strSQL = strSQLStart & strSQLFrom & strSQLWhere & strSQLOrder

Me.Form.RecordSource = strSQL


End Sub

Shall I invoke with the click button or on the form load? Please
verify. In the end I want this to open a report which is based on a
query.
Click to expand...
 
A

Addy

Yes I want to output this to a report. I have already laid out the
structure for the report.

Before passing this onto a report I need to correct my run time error.
When I select any criteria and run the button this is what it says.

Run-time error '3075'

Syntax Error (missing operator) in query expression '[hours
remaining].[target Hours] WHERE ([Hours Consumed].contractnumber =
"4545454")'

I think its a syntax problem with the where SQL. I have pasted it
below. Please help.

Thanks



If Not IsNull(Me.cbo_client) Then
strSQLWhere = "([Hours Consumed].[Sold to Customer Name] = """
& _
Me.cbo_client & """) AND "
End If

If Not IsNull(Me.txt_start_date) Then
strSQLWhere = strSQLWhere & "([Hours
Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If

If Not IsNull(Me.TXT_end_date) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractEndDate
< " & _
Format(Me.TXT_end_date + 1, strcJetDate) & ") AND "
End If

If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractNumber =
""" & _
Me.cbo_cont_num & """) AND"
End If

lngLen = Len(strSQLWhere) - 5 'Without trailing " AND "

If lngLen > 0 Then
strSQLWhere = " WHERE " & Left$(strSQLWhere, lngLen)
End If


strSQL = strSQLStart & strSQLFrom & strSQLWhere & strSQLOrder

Me.Form.RecordSource = strSQL


Allen said:
Hang on: do you want to see this in the form, or are you trying to output
this to a report?

The code you have is suited for the form, and should work when you click the
button.

If you want to then pass it to a report, you could put similar code in the
report's Open event (referring to the controls on the form instead of Me, of
course.) Or, in Access 2002 or 2003 you could pass the entire string from
the form to the report in the OpenArgs argument of OpenReport, and then use
Report_Open to apply it to the report's RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
Here is the code I came up with.

Private Sub Command16_Click()



strSQLStart = "TRANSFORM Sum([Hours Consumed].[Adjusted Hours])
AS [SumOfAdjusted Hours]SELECT [Hours Consumed].ContractNumber"
strSQLStart = strSQLStart & " , [Hours
Consumed].ContractEndDate"
strSQLStart = strSQLStart & " , [Hours
Consumed].ContractStartDate"
strSQLStart = strSQLStart & " , [Hours Consumed].[Service Order
Number]"
strSQLStart = strSQLStart & " , [Hours Consumed].[Sold to
Customer Name]"
strSQLStart = strSQLStart & " ,
Format([ActivityStartDate],""dddd"""", """"mmm d yyyy"") AS
ActivityStart"
strSQLStart = strSQLStart & " , [Hours Remaining].[Hours Used]"
strSQLStart = strSQLStart & " , [Hours
Remaining].HoursRemaining"
strSQLStart = strSQLStart & " , [Hours Remaining].[Target
Hours]"
strSQLStart = strSQLStart & " , Sum([Hours Consumed].[Adjusted
Hours]) AS [Adjusted Hours]"
strSQLFrom = " From [Hours Consumed] "
strSQLFrom = strSQLFrom & " INNER JOIN [Hours Remaining] "
strSQLFrom = strSQLFrom & " ON ([Hours Consumed].[Sold to
Customer Name] = [Hours Remaining].[Sold to Customer Name]) "
strSQLFrom = strSQLFrom & " AND ([Hours Consumed].ContractNumber
= [Hours Remaining].ContractNumber)"
strSQLFrom = strSQLFrom & " GROUP BY [Hours
Consumed].ContractNumber"
strSQLFrom = strSQLFrom & " , [Hours Consumed].ContractEndDate"
strSQLFrom = strSQLFrom & " , [Hours
Consumed].ContractStartDate"
strSQLFrom = strSQLFrom & " , [Hours Consumed].[Service Order
Number]"
strSQLFrom = strSQLFrom & " , [Hours Consumed].[Sold to Customer
Name]"
strSQLFrom = strSQLFrom & " ,
Format([ActivityStartDate],""dddd"""", """"mmm d yyyy"")"
strSQLFrom = strSQLFrom & " , [Hours Remaining].[Hours Used]"
strSQLFrom = strSQLFrom & " , [Hours Remaining].HoursRemaining"
strSQLFrom = strSQLFrom & " , [Hours Remaining].[Target Hours]"
strSQLOrder = " Order By [Hours Consumed].ContractNumber"
strSQLOrder = strSQLOrder & " , [Hours Consumed].[Service Order
Number]PIVOT [Hours Consumed].ActivityType;"



If Not IsNull(Me.cbo_client) Then
strSQLWhere = "([Hours Consumed].[Sold to Customer Name] = """
& _
Me.cbo_client & """) AND "
End If

If Not IsNull(Me.txt_start_date) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractStartDate
= " & _
Click to expand...
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If

If Not IsNull(Me.TXT_end_date) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractStartDate <
" & _
Format(Me.TXT_end_date + 1, strcJetDate) & ") AND "
End If

If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractNumber =
""" & _
Me.cbo_cont_num & """) AND "
End If

lngLen = Len(strSQLWhere) - 5 'Without trailing " AND "

If lngLen > 0 Then
strSQLWhere = " WHERE " & Left$(strSQLWhere, lngLen)
End If


strSQL = strSQLStart & strSQLFrom & strSQLWhere & strSQLOrder

Me.Form.RecordSource = strSQL


End Sub

Shall I invoke with the click button or on the form load? Please
verify. In the end I want this to open a report which is based on a
query.
Click to expand...
Click to expand...
 
A

Allen Browne

Presumably you have a strSQLStart, strSQLFrom, and strSQLOrder that we
cannot see here. The spaces will need to be right in the resultant string.

What is the data type of the ContractNumber field?
If it is a Number field (not a Text field), drop the extra quotes, i.e.:
If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractNumber =" & _
Me.cbo_cont_num & ") AND"
End If

If you still can't see what's wrong, add this as the second last line:
Debug.Print strSql
Then when it fails, you can copy it from the Immediate Window (Ctrl+G) into
a query, and play with it there until you identify what's wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
Yes I want to output this to a report. I have already laid out the
structure for the report.

Before passing this onto a report I need to correct my run time error.
When I select any criteria and run the button this is what it says.

Run-time error '3075'

Syntax Error (missing operator) in query expression '[hours
remaining].[target Hours] WHERE ([Hours Consumed].contractnumber =
"4545454")'

I think its a syntax problem with the where SQL. I have pasted it
below. Please help.

Thanks



If Not IsNull(Me.cbo_client) Then
strSQLWhere = "([Hours Consumed].[Sold to Customer Name] = """
& _
Me.cbo_client & """) AND "
End If

If Not IsNull(Me.txt_start_date) Then
strSQLWhere = strSQLWhere & "([Hours
Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If

If Not IsNull(Me.TXT_end_date) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractEndDate
< " & _
Format(Me.TXT_end_date + 1, strcJetDate) & ") AND "
End If

If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractNumber =
""" & _
Me.cbo_cont_num & """) AND"
End If

lngLen = Len(strSQLWhere) - 5 'Without trailing " AND "

If lngLen > 0 Then
strSQLWhere = " WHERE " & Left$(strSQLWhere, lngLen)
End If


strSQL = strSQLStart & strSQLFrom & strSQLWhere & strSQLOrder

Me.Form.RecordSource = strSQL


Allen said:
Hang on: do you want to see this in the form, or are you trying to output
this to a report?

The code you have is suited for the form, and should work when you click
the
button.

If you want to then pass it to a report, you could put similar code in
the
report's Open event (referring to the controls on the form instead of Me,
of
course.) Or, in Access 2002 or 2003 you could pass the entire string from
the form to the report in the OpenArgs argument of OpenReport, and then
use
Report_Open to apply it to the report's RecordSource.

Addy said:
Here is the code I came up with.

Private Sub Command16_Click()



strSQLStart = "TRANSFORM Sum([Hours Consumed].[Adjusted Hours])
AS [SumOfAdjusted Hours]SELECT [Hours Consumed].ContractNumber"
strSQLStart = strSQLStart & " , [Hours
Consumed].ContractEndDate"
strSQLStart = strSQLStart & " , [Hours
Consumed].ContractStartDate"
strSQLStart = strSQLStart & " , [Hours Consumed].[Service Order
Number]"
strSQLStart = strSQLStart & " , [Hours Consumed].[Sold to
Customer Name]"
strSQLStart = strSQLStart & " ,
Format([ActivityStartDate],""dddd"""", """"mmm d yyyy"") AS
ActivityStart"
strSQLStart = strSQLStart & " , [Hours Remaining].[Hours Used]"
strSQLStart = strSQLStart & " , [Hours
Remaining].HoursRemaining"
strSQLStart = strSQLStart & " , [Hours Remaining].[Target
Hours]"
strSQLStart = strSQLStart & " , Sum([Hours Consumed].[Adjusted
Hours]) AS [Adjusted Hours]"
strSQLFrom = " From [Hours Consumed] "
strSQLFrom = strSQLFrom & " INNER JOIN [Hours Remaining] "
strSQLFrom = strSQLFrom & " ON ([Hours Consumed].[Sold to
Customer Name] = [Hours Remaining].[Sold to Customer Name]) "
strSQLFrom = strSQLFrom & " AND ([Hours Consumed].ContractNumber
= [Hours Remaining].ContractNumber)"
strSQLFrom = strSQLFrom & " GROUP BY [Hours
Consumed].ContractNumber"
strSQLFrom = strSQLFrom & " , [Hours Consumed].ContractEndDate"
strSQLFrom = strSQLFrom & " , [Hours
Consumed].ContractStartDate"
strSQLFrom = strSQLFrom & " , [Hours Consumed].[Service Order
Number]"
strSQLFrom = strSQLFrom & " , [Hours Consumed].[Sold to Customer
Name]"
strSQLFrom = strSQLFrom & " ,
Format([ActivityStartDate],""dddd"""", """"mmm d yyyy"")"
strSQLFrom = strSQLFrom & " , [Hours Remaining].[Hours Used]"
strSQLFrom = strSQLFrom & " , [Hours Remaining].HoursRemaining"
strSQLFrom = strSQLFrom & " , [Hours Remaining].[Target Hours]"
strSQLOrder = " Order By [Hours Consumed].ContractNumber"
strSQLOrder = strSQLOrder & " , [Hours Consumed].[Service Order
Number]PIVOT [Hours Consumed].ActivityType;"



If Not IsNull(Me.cbo_client) Then
strSQLWhere = "([Hours Consumed].[Sold to Customer Name] = """
& _
Me.cbo_client & """) AND "
End If

If Not IsNull(Me.txt_start_date) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractStartDate
= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If

If Not IsNull(Me.TXT_end_date) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractStartDate <
" & _
Format(Me.TXT_end_date + 1, strcJetDate) & ") AND "
End If

If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractNumber =
""" & _
Me.cbo_cont_num & """) AND "
End If

lngLen = Len(strSQLWhere) - 5 'Without trailing " AND "

If lngLen > 0 Then
strSQLWhere = " WHERE " & Left$(strSQLWhere, lngLen)
End If


strSQL = strSQLStart & strSQLFrom & strSQLWhere & strSQLOrder

Me.Form.RecordSource = strSQL


End Sub

Shall I invoke with the click button or on the form load? Please
verify. In the end I want this to open a report which is based on a
query.
Click to expand...
Click to expand...
Click to expand...
 
A

Addy

Hey Allen,

Thanks alot for the Tip. I finally found my problem. I was making the
Group by clause appear before the where clause, which ended up in the
error.

Now my final challenge is to pass this code to this report I have
created.

So from what I understand I can use this code with the button_click and
pass the parameters.

I have included this at then end of the code.

DoCmd.OpenReport "Hours Consumed_Selection", acViewPreview, , , ,
strSQL

How will I get to the reports recordsource? I am using Acccess 2003.

Allen said:
Presumably you have a strSQLStart, strSQLFrom, and strSQLOrder that we
cannot see here. The spaces will need to be right in the resultant string.

What is the data type of the ContractNumber field?
If it is a Number field (not a Text field), drop the extra quotes, i.e.:
If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractNumber =" & _
Me.cbo_cont_num & ") AND"
End If

If you still can't see what's wrong, add this as the second last line:
Debug.Print strSql
Then when it fails, you can copy it from the Immediate Window (Ctrl+G) into
a query, and play with it there until you identify what's wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
Yes I want to output this to a report. I have already laid out the
structure for the report.

Before passing this onto a report I need to correct my run time error.
When I select any criteria and run the button this is what it says.

Run-time error '3075'

Syntax Error (missing operator) in query expression '[hours
remaining].[target Hours] WHERE ([Hours Consumed].contractnumber =
"4545454")'

I think its a syntax problem with the where SQL. I have pasted it
below. Please help.

Thanks



If Not IsNull(Me.cbo_client) Then
strSQLWhere = "([Hours Consumed].[Sold to Customer Name] = """
& _
Me.cbo_client & """) AND "
End If

If Not IsNull(Me.txt_start_date) Then
strSQLWhere = strSQLWhere & "([Hours
Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If

If Not IsNull(Me.TXT_end_date) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractEndDate
< " & _
Format(Me.TXT_end_date + 1, strcJetDate) & ") AND "
End If

If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractNumber =
""" & _
Me.cbo_cont_num & """) AND"
End If

lngLen = Len(strSQLWhere) - 5 'Without trailing " AND "

If lngLen > 0 Then
strSQLWhere = " WHERE " & Left$(strSQLWhere, lngLen)
End If


strSQL = strSQLStart & strSQLFrom & strSQLWhere & strSQLOrder

Me.Form.RecordSource = strSQL


Allen said:
Hang on: do you want to see this in the form, or are you trying to output
this to a report?

The code you have is suited for the form, and should work when you click
the
button.

If you want to then pass it to a report, you could put similar code in
the
report's Open event (referring to the controls on the form instead of Me,
of
course.) Or, in Access 2002 or 2003 you could pass the entire string from
the form to the report in the OpenArgs argument of OpenReport, and then
use
Report_Open to apply it to the report's RecordSource.

Here is the code I came up with.

Private Sub Command16_Click()



strSQLStart = "TRANSFORM Sum([Hours Consumed].[Adjusted Hours])
AS [SumOfAdjusted Hours]SELECT [Hours Consumed].ContractNumber"
strSQLStart = strSQLStart & " , [Hours
Consumed].ContractEndDate"
strSQLStart = strSQLStart & " , [Hours
Consumed].ContractStartDate"
strSQLStart = strSQLStart & " , [Hours Consumed].[Service Order
Number]"
strSQLStart = strSQLStart & " , [Hours Consumed].[Sold to
Customer Name]"
strSQLStart = strSQLStart & " ,
Format([ActivityStartDate],""dddd"""", """"mmm d yyyy"") AS
ActivityStart"
strSQLStart = strSQLStart & " , [Hours Remaining].[Hours Used]"
strSQLStart = strSQLStart & " , [Hours
Remaining].HoursRemaining"
strSQLStart = strSQLStart & " , [Hours Remaining].[Target
Hours]"
strSQLStart = strSQLStart & " , Sum([Hours Consumed].[Adjusted
Hours]) AS [Adjusted Hours]"
strSQLFrom = " From [Hours Consumed] "
strSQLFrom = strSQLFrom & " INNER JOIN [Hours Remaining] "
strSQLFrom = strSQLFrom & " ON ([Hours Consumed].[Sold to
Customer Name] = [Hours Remaining].[Sold to Customer Name]) "
strSQLFrom = strSQLFrom & " AND ([Hours Consumed].ContractNumber
= [Hours Remaining].ContractNumber)"
strSQLFrom = strSQLFrom & " GROUP BY [Hours
Consumed].ContractNumber"
strSQLFrom = strSQLFrom & " , [Hours Consumed].ContractEndDate"
strSQLFrom = strSQLFrom & " , [Hours
Consumed].ContractStartDate"
strSQLFrom = strSQLFrom & " , [Hours Consumed].[Service Order
Number]"
strSQLFrom = strSQLFrom & " , [Hours Consumed].[Sold to Customer
Name]"
strSQLFrom = strSQLFrom & " ,
Format([ActivityStartDate],""dddd"""", """"mmm d yyyy"")"
strSQLFrom = strSQLFrom & " , [Hours Remaining].[Hours Used]"
strSQLFrom = strSQLFrom & " , [Hours Remaining].HoursRemaining"
strSQLFrom = strSQLFrom & " , [Hours Remaining].[Target Hours]"
strSQLOrder = " Order By [Hours Consumed].ContractNumber"
strSQLOrder = strSQLOrder & " , [Hours Consumed].[Service Order
Number]PIVOT [Hours Consumed].ActivityType;"



If Not IsNull(Me.cbo_client) Then
strSQLWhere = "([Hours Consumed].[Sold to Customer Name] = """
& _
Me.cbo_client & """) AND "
End If

If Not IsNull(Me.txt_start_date) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractStartDate
= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If

If Not IsNull(Me.TXT_end_date) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractStartDate <
" & _
Format(Me.TXT_end_date + 1, strcJetDate) & ") AND "
End If

If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strWhere & "([Hours Consumed].ContractNumber =
""" & _
Me.cbo_cont_num & """) AND "
End If

lngLen = Len(strSQLWhere) - 5 'Without trailing " AND "

If lngLen > 0 Then
strSQLWhere = " WHERE " & Left$(strSQLWhere, lngLen)
End If


strSQL = strSQLStart & strSQLFrom & strSQLWhere & strSQLOrder

Me.Form.RecordSource = strSQL


End Sub

Shall I invoke with the click button or on the form load? Please
verify. In the end I want this to open a report which is based on a
query.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Oaky, you are passing the entire SQL statement in the report's OpenArgs.

In the report's Open event procedure, assign this to the report's
RecordSource:

Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs) > 0 Then
Me.RecordSource = Me.OpenArgs
End If
End Sub
 
A

Addy

Hi Allen,

I tried this. I am getting the following error

" Run-time Error '3637';

Cannot use the crosstab of a non fixed-fixed column as a subquery."
 
A

Addy

Never mind, I fixed this error myself using the ColumnHeading property.

Now things work fine, but when I requery the form it gives me the
following error.

Run Time error 3075

Invalid use of , . ! or () in query expression WHERE ([Hours
Consumed].ContractNumber = "40000222") ([Hours Consumed].ContractNumber
= "40000123")

I think I have to refresh the SQLstr. I have tried doing the following
at the end

strsql = ""
me.form.requery

So far nothing has worked.
 
A

Allen Browne

Is there supposed to be an AND or an OR in the WHERE clause, e.g.:
WHERE ([Hours Consumed].ContractNumber = "40000222")
OR ([Hours Consumed].ContractNumber = "40000123")


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
Never mind, I fixed this error myself using the ColumnHeading property.

Now things work fine, but when I requery the form it gives me the
following error.

Run Time error 3075

Invalid use of , . ! or () in query expression WHERE ([Hours
Consumed].ContractNumber = "40000222") ([Hours Consumed].ContractNumber
= "40000123")

I think I have to refresh the SQLstr. I have tried doing the following
at the end

strsql = ""
me.form.requery

So far nothing has worked.


Hi Allen,

I tried this. I am getting the following error

" Run-time Error '3637';

Cannot use the crosstab of a non fixed-fixed column as a subquery."
Click to expand...
Click to expand...
 
A

Addy

This is my where clause. is the extra AND the one causing this problem.


If Not IsNull(Me.cbo_client) Then
strSQLWhere = "([Hours Consumed].[Sold to Customer Name] = """
& _
Me.cbo_client & """) AND "
End If

If Not IsNull(Me.txt_start_date) Then
strSQLWhere = strSQLWhere & "([Hours
Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If

If Not IsNull(Me.TXT_end_date) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractEndDate
< " & _
Format(Me.TXT_end_date + 1, strcJetDate) & ") AND "
End If

If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractNumber =
""" & _
Me.cbo_cont_num & """) AND"
End If
Allen said:
Is there supposed to be an AND or an OR in the WHERE clause, e.g.:
WHERE ([Hours Consumed].ContractNumber = "40000222")
OR ([Hours Consumed].ContractNumber = "40000123")


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
Never mind, I fixed this error myself using the ColumnHeading property.

Now things work fine, but when I requery the form it gives me the
following error.

Run Time error 3075

Invalid use of , . ! or () in query expression WHERE ([Hours
Consumed].ContractNumber = "40000222") ([Hours Consumed].ContractNumber
= "40000123")

I think I have to refresh the SQLstr. I have tried doing the following
at the end

strsql = ""
me.form.requery

So far nothing has worked.


Hi Allen,

I tried this. I am getting the following error

" Run-time Error '3637';

Cannot use the crosstab of a non fixed-fixed column as a subquery."





Allen Browne wrote:
Oaky, you are passing the entire SQL statement in the report's
OpenArgs.

In the report's Open event procedure, assign this to the report's
RecordSource:

Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs) > 0 Then
Me.RecordSource = Me.OpenArgs
End If
End Sub

Hey Allen,

Thanks alot for the Tip. I finally found my problem. I was making the
Group by clause appear before the where clause, which ended up in the
error.

Now my final challenge is to pass this code to this report I have
created.

So from what I understand I can use this code with the button_click
and
pass the parameters.

I have included this at then end of the code.

DoCmd.OpenReport "Hours Consumed_Selection", acViewPreview, , , ,
strSQL

How will I get to the reports recordsource? I am using Acccess 2003.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

I might need to leave you to do your own debugging now.

Add the
Debug.Print strWhere
statement, and see what comes out in the Immediate window (Ctrl+G).

You can then see what's wrong and fix it.

All the best.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
This is my where clause. is the extra AND the one causing this problem.


If Not IsNull(Me.cbo_client) Then
strSQLWhere = "([Hours Consumed].[Sold to Customer Name] = """
& _
Me.cbo_client & """) AND "
End If

If Not IsNull(Me.txt_start_date) Then
strSQLWhere = strSQLWhere & "([Hours
Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If

If Not IsNull(Me.TXT_end_date) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractEndDate
< " & _
Format(Me.TXT_end_date + 1, strcJetDate) & ") AND "
End If

If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractNumber =
""" & _
Me.cbo_cont_num & """) AND"
End If
Allen said:
Is there supposed to be an AND or an OR in the WHERE clause, e.g.:
WHERE ([Hours Consumed].ContractNumber = "40000222")
OR ([Hours Consumed].ContractNumber = "40000123")
Click to expand...
Click to expand...
 
A

Addy

Ok I seem to have solved this problem. Here is the output from the
debug command.

WHERE WHERE ([Hours Consumed].ContractNumber = "40001517")([Hours
Consumed].ContractNumber = "40001519")

You can see the where is being printed twice when the form is used more
than once. For this all I did at the end was

strSQLWhere = ""

This seems to have done the trick. Is that the suggestion you were
suggesting?


Allen said:
I might need to leave you to do your own debugging now.

Add the
Debug.Print strWhere
statement, and see what comes out in the Immediate window (Ctrl+G).

You can then see what's wrong and fix it.

All the best.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Addy said:
This is my where clause. is the extra AND the one causing this problem.


If Not IsNull(Me.cbo_client) Then
strSQLWhere = "([Hours Consumed].[Sold to Customer Name] = """
& _
Me.cbo_client & """) AND "
End If

If Not IsNull(Me.txt_start_date) Then
strSQLWhere = strSQLWhere & "([Hours
Consumed].ContractStartDate >= " & _
Format(Me.txt_start_date, strcJetDate) & ") AND "
End If

If Not IsNull(Me.TXT_end_date) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractEndDate
< " & _
Format(Me.TXT_end_date + 1, strcJetDate) & ") AND "
End If

If Not IsNull(Me.cbo_cont_num) Then
strSQLWhere = strSQLWhere & "([Hours Consumed].ContractNumber =
""" & _
Me.cbo_cont_num & """) AND"
End If
Allen said:
Is there supposed to be an AND or an OR in the WHERE clause, e.g.:
WHERE ([Hours Consumed].ContractNumber = "40000222")
OR ([Hours Consumed].ContractNumber = "40000123")
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Launching a Form to Another Form to Create a New Record 12
Form Data Entry Problem 1
Question about Call Tracking design of form 0
Form question 1
Need help setting query criteria from a form 3
Parameter Query 7
Update Field On Differant Form 1
Passing a parameter from a subform to a new form? 3

Top