Can I specify Either/Or?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm not sure if this is possible or not. I have imported Duane Hookum's
DH_QBF (which is so fantastic if you're reading this Duane!) and am modifying
the queries to suit the reports that I need to make. I have a series of
fields that are joined together (see below). I have another field
Survey_System. If Survey_Sytem is "DLS" then I would like "Surface Location
DLS" to be what exports however, if Survey System is "NTS" then I would like
"Surface Location NTS" to export.

We will normally be exporting into Excel and I would like both of these to
show up under one column Surface Location, I am wondering if I can somehow
write this into the query. Am I expecting too much or is this possible?
Thanks a lot!!!!!

Surface Location DLS: [Tbl_MAIN]![Surf_LSD] & "-" & [Tbl_MAIN]![Surf_SEC] &
"-" & [Tbl_MAIN]![Surf_TWP] & "-" & [Tbl_MAIN]![Surf_RGE] & "" &
[Tbl_MAIN]![Surf_MER]

Surface Location NTS: [Tbl_MAIN]![Surf_QUnit] & "" &
[Tbl_MAIN]![Surf_Except] & "-" & [Tbl_MAIN]![Surf_Unit] & "-" &
[Tbl_MAIN]![Surf_4Block] & "/" & [Tbl_MAIN]![Surf_Map] & "-" &
[Tbl_MAIN]![Surf_6Block] & "-" & [Tbl_MAIN]![Surf_7Block]
 
Thanks for the nice compliment on the QBF. I have used it in almost every
application I create.

You can create another calculated column that uses an expression like:
Surface Location: IIf(Survey_Sytem = "DLS",[Tbl_MAIN]![Surf_LSD] & "-" &
[Tbl_MAIN]![Surf_SEC] &
"-" & [Tbl_MAIN]![Surf_TWP] & "-" & [Tbl_MAIN]![Surf_RGE] & "" &
[Tbl_MAIN]![Surf_MER], [Tbl_MAIN]![Surf_QUnit] & "" &
[Tbl_MAIN]![Surf_Except] & "-" & [Tbl_MAIN]![Surf_Unit] & "-" &
[Tbl_MAIN]![Surf_4Block] & "/" & [Tbl_MAIN]![Surf_Map] & "-" &
[Tbl_MAIN]![Surf_6Block] & "-" & [Tbl_MAIN]![Surf_7Block])
 
I had been puzzling over how I was going to build something similar with my
limited knowledge of code and just came across this by accident - it's almost
perfect for us. One thing I've noticed though is that it's limited to 30
fields. There are a couple of spreadsheets that even after having whittled
them down, have between 31 and 35 fields. Is there away around this limit?

Private Sub Form_AfterUpdate()
Dim intMaxColumns As Integer
intMaxColumns = 30 ' all that the results form will suppor
Me.AllowAdditions = Me.RecordsetClone.RecordCount < intMaxColumns
End Sub


Thanks again!

Duane Hookom said:
Thanks for the nice compliment on the QBF. I have used it in almost every
application I create.

You can create another calculated column that uses an expression like:
Surface Location: IIf(Survey_Sytem = "DLS",[Tbl_MAIN]![Surf_LSD] & "-" &
[Tbl_MAIN]![Surf_SEC] &
"-" & [Tbl_MAIN]![Surf_TWP] & "-" & [Tbl_MAIN]![Surf_RGE] & "" &
[Tbl_MAIN]![Surf_MER], [Tbl_MAIN]![Surf_QUnit] & "" &
[Tbl_MAIN]![Surf_Except] & "-" & [Tbl_MAIN]![Surf_Unit] & "-" &
[Tbl_MAIN]![Surf_4Block] & "/" & [Tbl_MAIN]![Surf_Map] & "-" &
[Tbl_MAIN]![Surf_6Block] & "-" & [Tbl_MAIN]![Surf_7Block])

--
Duane Hookom
Microsoft Access MVP


Carrie said:
Hi,

I'm not sure if this is possible or not. I have imported Duane Hookum's
DH_QBF (which is so fantastic if you're reading this Duane!) and am modifying
the queries to suit the reports that I need to make. I have a series of
fields that are joined together (see below). I have another field
Survey_System. If Survey_Sytem is "DLS" then I would like "Surface Location
DLS" to be what exports however, if Survey System is "NTS" then I would like
"Surface Location NTS" to export.

We will normally be exporting into Excel and I would like both of these to
show up under one column Surface Location, I am wondering if I can somehow
write this into the query. Am I expecting too much or is this possible?
Thanks a lot!!!!!

Surface Location DLS: [Tbl_MAIN]![Surf_LSD] & "-" & [Tbl_MAIN]![Surf_SEC] &
"-" & [Tbl_MAIN]![Surf_TWP] & "-" & [Tbl_MAIN]![Surf_RGE] & "" &
[Tbl_MAIN]![Surf_MER]

Surface Location NTS: [Tbl_MAIN]![Surf_QUnit] & "" &
[Tbl_MAIN]![Surf_Except] & "-" & [Tbl_MAIN]![Surf_Unit] & "-" &
[Tbl_MAIN]![Surf_4Block] & "/" & [Tbl_MAIN]![Surf_Map] & "-" &
[Tbl_MAIN]![Surf_6Block] & "-" & [Tbl_MAIN]![Surf_7Block]
Click to expand...
Click to expand...
 
I'm not sure which version of the QBF you have. You should be able to ignore
the limitation to the number of fields if the On Load event of
frmMyQueryResults has this code. Otherwise, you can change the code to the
following:
Private Sub Form_Open(Cancel As Integer)
Dim toss
On Error Resume Next
If Len(Me.OpenArgs & "") > 0 Then
strQueryName = Me.OpenArgs
Else
MsgBox "This feature expects a query name as an OpenArg.", vbOKOnly +
vbInformation, "Can't procede"
DoCmd.Close acForm, Me.Name
End If
Me.SubForm.SourceObject = "query." & strQueryName
'if the frmQBFSource is open, grab the query for the form caption
Me.Caption = Forms![frmQBFSource]![QBFName]
Me.TimerInterval = 0
End Sub

--
Duane Hookom
Microsoft Access MVP


Carrie said:
I had been puzzling over how I was going to build something similar with my
limited knowledge of code and just came across this by accident - it's almost
perfect for us. One thing I've noticed though is that it's limited to 30
fields. There are a couple of spreadsheets that even after having whittled
them down, have between 31 and 35 fields. Is there away around this limit?

Private Sub Form_AfterUpdate()
Dim intMaxColumns As Integer
intMaxColumns = 30 ' all that the results form will suppor
Me.AllowAdditions = Me.RecordsetClone.RecordCount < intMaxColumns
End Sub


Thanks again!

Duane Hookom said:
Thanks for the nice compliment on the QBF. I have used it in almost every
application I create.

You can create another calculated column that uses an expression like:
Surface Location: IIf(Survey_Sytem = "DLS",[Tbl_MAIN]![Surf_LSD] & "-" &
[Tbl_MAIN]![Surf_SEC] &
"-" & [Tbl_MAIN]![Surf_TWP] & "-" & [Tbl_MAIN]![Surf_RGE] & "" &
[Tbl_MAIN]![Surf_MER], [Tbl_MAIN]![Surf_QUnit] & "" &
[Tbl_MAIN]![Surf_Except] & "-" & [Tbl_MAIN]![Surf_Unit] & "-" &
[Tbl_MAIN]![Surf_4Block] & "/" & [Tbl_MAIN]![Surf_Map] & "-" &
[Tbl_MAIN]![Surf_6Block] & "-" & [Tbl_MAIN]![Surf_7Block])

--
Duane Hookom
Microsoft Access MVP


Carrie said:
Hi,

I'm not sure if this is possible or not. I have imported Duane Hookum's
DH_QBF (which is so fantastic if you're reading this Duane!) and am modifying
the queries to suit the reports that I need to make. I have a series of
fields that are joined together (see below). I have another field
Survey_System. If Survey_Sytem is "DLS" then I would like "Surface Location
DLS" to be what exports however, if Survey System is "NTS" then I would like
"Surface Location NTS" to export.

We will normally be exporting into Excel and I would like both of these to
show up under one column Surface Location, I am wondering if I can somehow
write this into the query. Am I expecting too much or is this possible?
Thanks a lot!!!!!

Surface Location DLS: [Tbl_MAIN]![Surf_LSD] & "-" & [Tbl_MAIN]![Surf_SEC] &
"-" & [Tbl_MAIN]![Surf_TWP] & "-" & [Tbl_MAIN]![Surf_RGE] & "" &
[Tbl_MAIN]![Surf_MER]

Surface Location NTS: [Tbl_MAIN]![Surf_QUnit] & "" &
[Tbl_MAIN]![Surf_Except] & "-" & [Tbl_MAIN]![Surf_Unit] & "-" &
[Tbl_MAIN]![Surf_4Block] & "/" & [Tbl_MAIN]![Surf_Map] & "-" &
[Tbl_MAIN]![Surf_6Block] & "-" & [Tbl_MAIN]![Surf_7Block]
Click to expand...
Click to expand...
Click to expand...
 
Thanks again! I'll give this a try.

The version I found is Version 2.0 March 2004. Is there something newer I
should download?

Duane Hookom said:
I'm not sure which version of the QBF you have. You should be able to ignore
the limitation to the number of fields if the On Load event of
frmMyQueryResults has this code. Otherwise, you can change the code to the
following:
Private Sub Form_Open(Cancel As Integer)
Dim toss
On Error Resume Next
If Len(Me.OpenArgs & "") > 0 Then
strQueryName = Me.OpenArgs
Else
MsgBox "This feature expects a query name as an OpenArg.", vbOKOnly +
vbInformation, "Can't procede"
DoCmd.Close acForm, Me.Name
End If
Me.SubForm.SourceObject = "query." & strQueryName
'if the frmQBFSource is open, grab the query for the form caption
Me.Caption = Forms![frmQBFSource]![QBFName]
Me.TimerInterval = 0
End Sub

--
Duane Hookom
Microsoft Access MVP


Carrie said:
I had been puzzling over how I was going to build something similar with my
limited knowledge of code and just came across this by accident - it's almost
perfect for us. One thing I've noticed though is that it's limited to 30
fields. There are a couple of spreadsheets that even after having whittled
them down, have between 31 and 35 fields. Is there away around this limit?

Private Sub Form_AfterUpdate()
Dim intMaxColumns As Integer
intMaxColumns = 30 ' all that the results form will suppor
Me.AllowAdditions = Me.RecordsetClone.RecordCount < intMaxColumns
End Sub


Thanks again!

Duane Hookom said:
Thanks for the nice compliment on the QBF. I have used it in almost every
application I create.

You can create another calculated column that uses an expression like:
Surface Location: IIf(Survey_Sytem = "DLS",[Tbl_MAIN]![Surf_LSD] & "-" &
[Tbl_MAIN]![Surf_SEC] &
"-" & [Tbl_MAIN]![Surf_TWP] & "-" & [Tbl_MAIN]![Surf_RGE] & "" &
[Tbl_MAIN]![Surf_MER], [Tbl_MAIN]![Surf_QUnit] & "" &
[Tbl_MAIN]![Surf_Except] & "-" & [Tbl_MAIN]![Surf_Unit] & "-" &
[Tbl_MAIN]![Surf_4Block] & "/" & [Tbl_MAIN]![Surf_Map] & "-" &
[Tbl_MAIN]![Surf_6Block] & "-" & [Tbl_MAIN]![Surf_7Block])

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I'm not sure if this is possible or not. I have imported Duane Hookum's
DH_QBF (which is so fantastic if you're reading this Duane!) and am modifying
the queries to suit the reports that I need to make. I have a series of
fields that are joined together (see below). I have another field
Survey_System. If Survey_Sytem is "DLS" then I would like "Surface Location
DLS" to be what exports however, if Survey System is "NTS" then I would like
"Surface Location NTS" to export.

We will normally be exporting into Excel and I would like both of these to
show up under one column Surface Location, I am wondering if I can somehow
write this into the query. Am I expecting too much or is this possible?
Thanks a lot!!!!!

Surface Location DLS: [Tbl_MAIN]![Surf_LSD] & "-" & [Tbl_MAIN]![Surf_SEC] &
"-" & [Tbl_MAIN]![Surf_TWP] & "-" & [Tbl_MAIN]![Surf_RGE] & "" &
[Tbl_MAIN]![Surf_MER]

Surface Location NTS: [Tbl_MAIN]![Surf_QUnit] & "" &
[Tbl_MAIN]![Surf_Except] & "-" & [Tbl_MAIN]![Surf_Unit] & "-" &
[Tbl_MAIN]![Surf_4Block] & "/" & [Tbl_MAIN]![Surf_Map] & "-" &
[Tbl_MAIN]![Surf_6Block] & "-" & [Tbl_MAIN]![Surf_7Block]
Click to expand...
Click to expand...
Click to expand...
 
The only significant change was how the subform is rendered. I'm not sure how
current the version is at RogersAccessLibrary.
--
Duane Hookom
Microsoft Access MVP


Carrie said:
Thanks again! I'll give this a try.

The version I found is Version 2.0 March 2004. Is there something newer I
should download?

Duane Hookom said:
I'm not sure which version of the QBF you have. You should be able to ignore
the limitation to the number of fields if the On Load event of
frmMyQueryResults has this code. Otherwise, you can change the code to the
following:
Private Sub Form_Open(Cancel As Integer)
Dim toss
On Error Resume Next
If Len(Me.OpenArgs & "") > 0 Then
strQueryName = Me.OpenArgs
Else
MsgBox "This feature expects a query name as an OpenArg.", vbOKOnly +
vbInformation, "Can't procede"
DoCmd.Close acForm, Me.Name
End If
Me.SubForm.SourceObject = "query." & strQueryName
'if the frmQBFSource is open, grab the query for the form caption
Me.Caption = Forms![frmQBFSource]![QBFName]
Me.TimerInterval = 0
End Sub

--
Duane Hookom
Microsoft Access MVP


Carrie said:
I had been puzzling over how I was going to build something similar with my
limited knowledge of code and just came across this by accident - it's almost
perfect for us. One thing I've noticed though is that it's limited to 30
fields. There are a couple of spreadsheets that even after having whittled
them down, have between 31 and 35 fields. Is there away around this limit?

Private Sub Form_AfterUpdate()
Dim intMaxColumns As Integer
intMaxColumns = 30 ' all that the results form will suppor
Me.AllowAdditions = Me.RecordsetClone.RecordCount < intMaxColumns
End Sub


Thanks again!

:

Thanks for the nice compliment on the QBF. I have used it in almost every
application I create.

You can create another calculated column that uses an expression like:
Surface Location: IIf(Survey_Sytem = "DLS",[Tbl_MAIN]![Surf_LSD] & "-" &
[Tbl_MAIN]![Surf_SEC] &
"-" & [Tbl_MAIN]![Surf_TWP] & "-" & [Tbl_MAIN]![Surf_RGE] & "" &
[Tbl_MAIN]![Surf_MER], [Tbl_MAIN]![Surf_QUnit] & "" &
[Tbl_MAIN]![Surf_Except] & "-" & [Tbl_MAIN]![Surf_Unit] & "-" &
[Tbl_MAIN]![Surf_4Block] & "/" & [Tbl_MAIN]![Surf_Map] & "-" &
[Tbl_MAIN]![Surf_6Block] & "-" & [Tbl_MAIN]![Surf_7Block])

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I'm not sure if this is possible or not. I have imported Duane Hookum's
DH_QBF (which is so fantastic if you're reading this Duane!) and am modifying
the queries to suit the reports that I need to make. I have a series of
fields that are joined together (see below). I have another field
Survey_System. If Survey_Sytem is "DLS" then I would like "Surface Location
DLS" to be what exports however, if Survey System is "NTS" then I would like
"Surface Location NTS" to export.

We will normally be exporting into Excel and I would like both of these to
show up under one column Surface Location, I am wondering if I can somehow
write this into the query. Am I expecting too much or is this possible?
Thanks a lot!!!!!

Surface Location DLS: [Tbl_MAIN]![Surf_LSD] & "-" & [Tbl_MAIN]![Surf_SEC] &
"-" & [Tbl_MAIN]![Surf_TWP] & "-" & [Tbl_MAIN]![Surf_RGE] & "" &
[Tbl_MAIN]![Surf_MER]

Surface Location NTS: [Tbl_MAIN]![Surf_QUnit] & "" &
[Tbl_MAIN]![Surf_Except] & "-" & [Tbl_MAIN]![Surf_Unit] & "-" &
[Tbl_MAIN]![Surf_4Block] & "/" & [Tbl_MAIN]![Surf_Map] & "-" &
[Tbl_MAIN]![Surf_6Block] & "-" & [Tbl_MAIN]![Surf_7Block]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top