Where clause with 2 criteria

M

Mike Revis

Hi Group,

Access 2007. Win xppro

I cannot figure out how to add a second where clause.

I have this to loop through the recordset returned by JobNumber. There can
be multiple package records within the JobNumber recordset.

This is the part of the code in the page footer format section of the report
that I am trying to alter.

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'"

JobNumber is text data type.

It shows on the report as UNorIDNumber = NAERG. UN1234 = 123

The query that the report is based on is set to exclude records where the
field IsDG = false.
IsDG is a yes/no data type.

This code above does not exclude those records. So I am getting an = without
the data.

If I have 2 records and one has IsDG = false I get = UN1234 = 123

I have tried various iterations of *AND WHERE
Forms!frmpackage!sfrmPart.Form!IsDG = True* with little success.

The fields UnorIDNumber, NAERG and IsDG are all on the subform sfrmPart and
in the table tblPartData.

The closest I have come is "data type mismatch"

I suspect it has something to do with those pesky little &"&' " " 's which I
could never get the hang of.

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike
 
D

Douglas J. Steele

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "' " &
_
"AND Forms!frmpackage!sfrmPart.Form!IsDG = True"

What are you doing with the SQL, though? In some case, you may need to use

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "' " &
_
"AND " & Forms!frmpackage!sfrmPart.Form!IsDG & " = True"

On the other hand, can't you just check in the code whether IsDG is true, as
opposed to needing to do it in the SQL?
 
M

Mike Revis

Thanks Doug,

The first example you provided gave an error of "To few parameters. Expected
1." The second example processed ok but did not exclude the =.

I am trying to get the SQL to select only the records where IsDG = True.

In my messing around I have discovered that the SQL does NOT exclude the
records where IsDG = False.
It shows all of the records regardless of IsDG.
I am getting the unwanted = because there is no data in the fields
UNorIDNumber and NAERG. If I put data in those fields it shows up even when
IsDG = False.

I know. Tables have fields. Forms have controls. Forgive me.

Back to the drawing board. :(

This is my setup.

Each JobNumber can have one or many packages.
Each package can have one or many parts.
frmJob. tblJobData
frmpackage. tblPackageData
sfrmPart. tblPartData

The fields IsDG, UNorIDNumber and NAERG are on the sfrmPart.
Some parts will be IsDG = True.
Some parts will be IsDG = False.

I prevent the records where IsDG = False from showing in the detail section
by setting the criteria for IsDG to =True in the reports query.

This data will be in an unbound control in the page footer named
txtidnumber.

When a part has IsDG = True then I want [UNorIDNumber] = [NAERG] to show.
UN1234= 123.
When there are multiple parts where IsDG= True then it would look
like....UN1234= 123 UN7890= 567 etc...
If a part has IsDG= False then I want nothing to show for that part.

Do you think my code (shown below) can be modified or should I start over?

Sometimes I discover the answer in the process of forming the question. This
time I'm not sure I've even formed the question in an understandable manner.


As always thank you for your time and consideration.

Best regards,
Mike






'This was originally written in Access 97.


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strID As String

'Start NAERG

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

strID = " "
Do Until rs.EOF
strID = strID & rs![UNorIDNumber] & "= " & rs![NAERG] & " "
rs.MoveNext
Loop
strID = Left(strID, Len(strID) - 1)
Reports!rptiata_plain!txtidnumber = strID

'End NAERG






Douglas J. Steele said:
strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " &
_
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "' "
& _
"AND Forms!frmpackage!sfrmPart.Form!IsDG = True"

What are you doing with the SQL, though? In some case, you may need to use

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " &
_
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "' "
& _
"AND " & Forms!frmpackage!sfrmPart.Form!IsDG & " = True"

On the other hand, can't you just check in the code whether IsDG is true,
as opposed to needing to do it in the SQL?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mike Revis said:
Hi Group,

Access 2007. Win xppro

I cannot figure out how to add a second where clause.

I have this to loop through the recordset returned by JobNumber. There
can be multiple package records within the JobNumber recordset.

This is the part of the code in the page footer format section of the
report that I am trying to alter.

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " &
_
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'"

JobNumber is text data type.

It shows on the report as UNorIDNumber = NAERG. UN1234 = 123

The query that the report is based on is set to exclude records where the
field IsDG = false.
IsDG is a yes/no data type.

This code above does not exclude those records. So I am getting an =
without the data.

If I have 2 records and one has IsDG = false I get = UN1234 = 123

I have tried various iterations of *AND WHERE
Forms!frmpackage!sfrmPart.Form!IsDG = True* with little success.

The fields UnorIDNumber, NAERG and IsDG are all on the subform sfrmPart
and in the table tblPartData.

The closest I have come is "data type mismatch"

I suspect it has something to do with those pesky little &"&' " " 's
which I could never get the hang of.

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry, I really can't follow what you're trying to do.

However, it does sound as though you're trying to use the control on the
form rather than the field in the table. Does it work if you use

strSQL = "SELECT DISTINCT NAERG, UNorIDNumber " & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "' " & _
"AND IsDG = True"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mike Revis said:
Thanks Doug,

The first example you provided gave an error of "To few parameters.
Expected 1." The second example processed ok but did not exclude the =.

I am trying to get the SQL to select only the records where IsDG = True.

In my messing around I have discovered that the SQL does NOT exclude the
records where IsDG = False.
It shows all of the records regardless of IsDG.
I am getting the unwanted = because there is no data in the fields
UNorIDNumber and NAERG. If I put data in those fields it shows up even
when IsDG = False.

I know. Tables have fields. Forms have controls. Forgive me.

Back to the drawing board. :(

This is my setup.

Each JobNumber can have one or many packages.
Each package can have one or many parts.
frmJob. tblJobData
frmpackage. tblPackageData
sfrmPart. tblPartData

The fields IsDG, UNorIDNumber and NAERG are on the sfrmPart.
Some parts will be IsDG = True.
Some parts will be IsDG = False.

I prevent the records where IsDG = False from showing in the detail
section by setting the criteria for IsDG to =True in the reports query.

This data will be in an unbound control in the page footer named
txtidnumber.

When a part has IsDG = True then I want [UNorIDNumber] = [NAERG] to show.
UN1234= 123.
When there are multiple parts where IsDG= True then it would look
like....UN1234= 123 UN7890= 567 etc...
If a part has IsDG= False then I want nothing to show for that part.

Do you think my code (shown below) can be modified or should I start over?

Sometimes I discover the answer in the process of forming the question.
This time I'm not sure I've even formed the question in an understandable
manner.


As always thank you for your time and consideration.

Best regards,
Mike






'This was originally written in Access 97.


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strID As String

'Start NAERG

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " &
_
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

strID = " "
Do Until rs.EOF
strID = strID & rs![UNorIDNumber] & "= " & rs![NAERG] & " "
rs.MoveNext
Loop
strID = Left(strID, Len(strID) - 1)
Reports!rptiata_plain!txtidnumber = strID

'End NAERG






Douglas J. Steele said:
strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " &
_
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "' "
& _
"AND Forms!frmpackage!sfrmPart.Form!IsDG = True"

What are you doing with the SQL, though? In some case, you may need to
use

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " &
_
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "' "
& _
"AND " & Forms!frmpackage!sfrmPart.Form!IsDG & " = True"

On the other hand, can't you just check in the code whether IsDG is true,
as opposed to needing to do it in the SQL?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mike Revis said:
Hi Group,

Access 2007. Win xppro

I cannot figure out how to add a second where clause.

I have this to loop through the recordset returned by JobNumber. There
can be multiple package records within the JobNumber recordset.

This is the part of the code in the page footer format section of the
report that I am trying to alter.

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber "
& _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'"

JobNumber is text data type.

It shows on the report as UNorIDNumber = NAERG. UN1234 = 123

The query that the report is based on is set to exclude records where
the field IsDG = false.
IsDG is a yes/no data type.

This code above does not exclude those records. So I am getting an =
without the data.

If I have 2 records and one has IsDG = false I get = UN1234 = 123

I have tried various iterations of *AND WHERE
Forms!frmpackage!sfrmPart.Form!IsDG = True* with little success.

The fields UnorIDNumber, NAERG and IsDG are all on the subform sfrmPart
and in the table tblPartData.

The closest I have come is "data type mismatch"

I suspect it has something to do with those pesky little &"&' " " 's
which I could never get the hang of.

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike
Click to expand...
Click to expand...
Click to expand...
 
M

Mike Revis

Doug,
I know. I couldn't express the situation very well.

I think I have found the solution though.

I added the revised AND line below to the original code.


strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'" &
_
"AND tblPartData.IsDG = True"

I don't know how or why but so far my testing has met with the desired
result.

Thank you again for your time and consideration.

Best regards,
Mike





Douglas J. Steele said:
Sorry, I really can't follow what you're trying to do.

However, it does sound as though you're trying to use the control on the
form rather than the field in the table. Does it work if you use

strSQL = "SELECT DISTINCT NAERG, UNorIDNumber " & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "' " & _
"AND IsDG = True"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mike Revis said:
Thanks Doug,

The first example you provided gave an error of "To few parameters.
Expected 1." The second example processed ok but did not exclude the =.

I am trying to get the SQL to select only the records where IsDG = True.

In my messing around I have discovered that the SQL does NOT exclude the
records where IsDG = False.
It shows all of the records regardless of IsDG.
I am getting the unwanted = because there is no data in the fields
UNorIDNumber and NAERG. If I put data in those fields it shows up even
when IsDG = False.

I know. Tables have fields. Forms have controls. Forgive me.

Back to the drawing board. :(

This is my setup.

Each JobNumber can have one or many packages.
Each package can have one or many parts.
frmJob. tblJobData
frmpackage. tblPackageData
sfrmPart. tblPartData

The fields IsDG, UNorIDNumber and NAERG are on the sfrmPart.
Some parts will be IsDG = True.
Some parts will be IsDG = False.

I prevent the records where IsDG = False from showing in the detail
section by setting the criteria for IsDG to =True in the reports query.

This data will be in an unbound control in the page footer named
txtidnumber.

When a part has IsDG = True then I want [UNorIDNumber] = [NAERG] to show.
UN1234= 123.
When there are multiple parts where IsDG= True then it would look
like....UN1234= 123 UN7890= 567 etc...
If a part has IsDG= False then I want nothing to show for that part.

Do you think my code (shown below) can be modified or should I start
over?

Sometimes I discover the answer in the process of forming the question.
This time I'm not sure I've even formed the question in an understandable
manner.


As always thank you for your time and consideration.

Best regards,
Mike






'This was originally written in Access 97.


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strID As String

'Start NAERG

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " &
_
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

strID = " "
Do Until rs.EOF
strID = strID & rs![UNorIDNumber] & "= " & rs![NAERG] & " "
rs.MoveNext
Loop
strID = Left(strID, Len(strID) - 1)
Reports!rptiata_plain!txtidnumber = strID

'End NAERG






Douglas J. Steele said:
strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber "
& _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'
" & _
"AND Forms!frmpackage!sfrmPart.Form!IsDG = True"

What are you doing with the SQL, though? In some case, you may need to
use

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber "
& _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'
" & _
"AND " & Forms!frmpackage!sfrmPart.Form!IsDG & " = True"

On the other hand, can't you just check in the code whether IsDG is
true, as opposed to needing to do it in the SQL?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Group,

Access 2007. Win xppro

I cannot figure out how to add a second where clause.

I have this to loop through the recordset returned by JobNumber. There
can be multiple package records within the JobNumber recordset.

This is the part of the code in the page footer format section of the
report that I am trying to alter.

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber "
& _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] &
"'"

JobNumber is text data type.

It shows on the report as UNorIDNumber = NAERG. UN1234 = 123

The query that the report is based on is set to exclude records where
the field IsDG = false.
IsDG is a yes/no data type.

This code above does not exclude those records. So I am getting an =
without the data.

If I have 2 records and one has IsDG = false I get = UN1234 = 123

I have tried various iterations of *AND WHERE
Forms!frmpackage!sfrmPart.Form!IsDG = True* with little success.

The fields UnorIDNumber, NAERG and IsDG are all on the subform sfrmPart
and in the table tblPartData.

The closest I have come is "data type mismatch"

I suspect it has something to do with those pesky little &"&' " " 's
which I could never get the hang of.

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Glad you got it working. Note that that's what I'd suggested on Sunday...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mike Revis said:
Doug,
I know. I couldn't express the situation very well.

I think I have found the solution though.

I added the revised AND line below to the original code.


strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " &
_
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'" &
_
"AND tblPartData.IsDG = True"

I don't know how or why but so far my testing has met with the desired
result.

Thank you again for your time and consideration.

Best regards,
Mike





Douglas J. Steele said:
Sorry, I really can't follow what you're trying to do.

However, it does sound as though you're trying to use the control on the
form rather than the field in the table. Does it work if you use

strSQL = "SELECT DISTINCT NAERG, UNorIDNumber " & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "' " & _
"AND IsDG = True"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mike Revis said:
Thanks Doug,

The first example you provided gave an error of "To few parameters.
Expected 1." The second example processed ok but did not exclude the =.

I am trying to get the SQL to select only the records where IsDG = True.

In my messing around I have discovered that the SQL does NOT exclude the
records where IsDG = False.
It shows all of the records regardless of IsDG.
I am getting the unwanted = because there is no data in the fields
UNorIDNumber and NAERG. If I put data in those fields it shows up even
when IsDG = False.

I know. Tables have fields. Forms have controls. Forgive me.

Back to the drawing board. :(

This is my setup.

Each JobNumber can have one or many packages.
Each package can have one or many parts.
frmJob. tblJobData
frmpackage. tblPackageData
sfrmPart. tblPartData

The fields IsDG, UNorIDNumber and NAERG are on the sfrmPart.
Some parts will be IsDG = True.
Some parts will be IsDG = False.

I prevent the records where IsDG = False from showing in the detail
section by setting the criteria for IsDG to =True in the reports query.

This data will be in an unbound control in the page footer named
txtidnumber.

When a part has IsDG = True then I want [UNorIDNumber] = [NAERG] to
show. UN1234= 123.
When there are multiple parts where IsDG= True then it would look
like....UN1234= 123 UN7890= 567 etc...
If a part has IsDG= False then I want nothing to show for that part.

Do you think my code (shown below) can be modified or should I start
over?

Sometimes I discover the answer in the process of forming the question.
This time I'm not sure I've even formed the question in an
understandable manner.


As always thank you for your time and consideration.

Best regards,
Mike






'This was originally written in Access 97.


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strID As String

'Start NAERG

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber "
& _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

strID = " "
Do Until rs.EOF
strID = strID & rs![UNorIDNumber] & "= " & rs![NAERG] & " "
rs.MoveNext
Loop
strID = Left(strID, Len(strID) - 1)
Reports!rptiata_plain!txtidnumber = strID

'End NAERG






strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber "
& _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'
" & _
"AND Forms!frmpackage!sfrmPart.Form!IsDG = True"

What are you doing with the SQL, though? In some case, you may need to
use

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber "
& _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'
" & _
"AND " & Forms!frmpackage!sfrmPart.Form!IsDG & " = True"

On the other hand, can't you just check in the code whether IsDG is
true, as opposed to needing to do it in the SQL?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Group,

Access 2007. Win xppro

I cannot figure out how to add a second where clause.

I have this to loop through the recordset returned by JobNumber. There
can be multiple package records within the JobNumber recordset.

This is the part of the code in the page footer format section of the
report that I am trying to alter.

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber
" & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] &
"'"

JobNumber is text data type.

It shows on the report as UNorIDNumber = NAERG. UN1234 = 123

The query that the report is based on is set to exclude records where
the field IsDG = false.
IsDG is a yes/no data type.

This code above does not exclude those records. So I am getting an =
without the data.

If I have 2 records and one has IsDG = false I get = UN1234 = 123

I have tried various iterations of *AND WHERE
Forms!frmpackage!sfrmPart.Form!IsDG = True* with little success.

The fields UnorIDNumber, NAERG and IsDG are all on the subform
sfrmPart and in the table tblPartData.

The closest I have come is "data type mismatch"

I suspect it has something to do with those pesky little &"&' " " 's
which I could never get the hang of.

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike
Click to expand...
Click to expand...
Click to expand...
 
M

Mike Revis

Noted. :)

Douglas J. Steele said:
Glad you got it working. Note that that's what I'd suggested on Sunday...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mike Revis said:
Doug,
I know. I couldn't express the situation very well.

I think I have found the solution though.

I added the revised AND line below to the original code.


strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber " &
_
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "'"
& _
"AND tblPartData.IsDG = True"

I don't know how or why but so far my testing has met with the desired
result.

Thank you again for your time and consideration.

Best regards,
Mike





Douglas J. Steele said:
Sorry, I really can't follow what you're trying to do.

However, it does sound as though you're trying to use the control on the
form rather than the field in the table. Does it work if you use

strSQL = "SELECT DISTINCT NAERG, UNorIDNumber " & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] & "' " & _
"AND IsDG = True"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Doug,

The first example you provided gave an error of "To few parameters.
Expected 1." The second example processed ok but did not exclude the =.

I am trying to get the SQL to select only the records where IsDG =
True.

In my messing around I have discovered that the SQL does NOT exclude
the records where IsDG = False.
It shows all of the records regardless of IsDG.
I am getting the unwanted = because there is no data in the fields
UNorIDNumber and NAERG. If I put data in those fields it shows up even
when IsDG = False.

I know. Tables have fields. Forms have controls. Forgive me.

Back to the drawing board. :(

This is my setup.

Each JobNumber can have one or many packages.
Each package can have one or many parts.
frmJob. tblJobData
frmpackage. tblPackageData
sfrmPart. tblPartData

The fields IsDG, UNorIDNumber and NAERG are on the sfrmPart.
Some parts will be IsDG = True.
Some parts will be IsDG = False.

I prevent the records where IsDG = False from showing in the detail
section by setting the criteria for IsDG to =True in the reports query.

This data will be in an unbound control in the page footer named
txtidnumber.

When a part has IsDG = True then I want [UNorIDNumber] = [NAERG] to
show. UN1234= 123.
When there are multiple parts where IsDG= True then it would look
like....UN1234= 123 UN7890= 567 etc...
If a part has IsDG= False then I want nothing to show for that part.

Do you think my code (shown below) can be modified or should I start
over?

Sometimes I discover the answer in the process of forming the question.
This time I'm not sure I've even formed the question in an
understandable manner.


As always thank you for your time and consideration.

Best regards,
Mike






'This was originally written in Access 97.


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strID As String

'Start NAERG

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber "
& _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] &
"'"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

strID = " "
Do Until rs.EOF
strID = strID & rs![UNorIDNumber] & "= " & rs![NAERG] & " "
rs.MoveNext
Loop
strID = Left(strID, Len(strID) - 1)
Reports!rptiata_plain!txtidnumber = strID

'End NAERG






strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber
" & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] &
"' " & _
"AND Forms!frmpackage!sfrmPart.Form!IsDG = True"

What are you doing with the SQL, though? In some case, you may need to
use

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber
" & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] &
"' " & _
"AND " & Forms!frmpackage!sfrmPart.Form!IsDG & " = True"

On the other hand, can't you just check in the code whether IsDG is
true, as opposed to needing to do it in the SQL?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Group,

Access 2007. Win xppro

I cannot figure out how to add a second where clause.

I have this to loop through the recordset returned by JobNumber.
There can be multiple package records within the JobNumber recordset.

This is the part of the code in the page footer format section of the
report that I am trying to alter.

strSQL = "SELECT DISTINCT tblPartData.NAERG, tblPartData.UNorIDNumber
" & _
"FROM tblPartData " & _
"WHERE [JobNumber]= '" & Forms!frmpackage![JobNumber] &
"'"

JobNumber is text data type.

It shows on the report as UNorIDNumber = NAERG. UN1234 = 123

The query that the report is based on is set to exclude records where
the field IsDG = false.
IsDG is a yes/no data type.

This code above does not exclude those records. So I am getting an =
without the data.

If I have 2 records and one has IsDG = false I get = UN1234 =
123

I have tried various iterations of *AND WHERE
Forms!frmpackage!sfrmPart.Form!IsDG = True* with little success.

The fields UnorIDNumber, NAERG and IsDG are all on the subform
sfrmPart and in the table tblPartData.

The closest I have come is "data type mismatch"

I suspect it has something to do with those pesky little &"&' " " 's
which I could never get the hang of.

As always any thoughts, comments or suggestions are welcome.

Best regards,
Mike
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

Date Criteria problem 2
Continuous Form & Combo box question 1
WHERE clause returns A and B, but not A and C 4
Open form based on query result 4
Problem with Where Clause/Filter in a Report 2
Where Clause with the OpenReport command 1
Report based on criteria form 5
WHERE clause 5

Top