Multiple If statements in the criteria of a query

I'm trying to instert the following in the criteria section of a query in
query builder; the itself is the record source for a list box
*******************************************************************************************************
If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = "Ablation"
Then

    Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _
      "WHERE fldRNnotesCode = 'A'" & "ORDER BY [fld.order]"
       
         Else: If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType
= "Device" Then
    
                    Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU "
& _
                         "WHERE fldRNnotesCode = 'D'" & "ORDER BY [fld.order]
"

                End If
           End If
****************************************************************************************************
I started this by placing it in the onOpen event of the form, but then I
realized I don't want to hard code this in the form. I'd rather to just be
able to do it within a query. Then if I add new VisitTypes I can simply
modify the query without touching the code in the form. 

I know my criteria statement is wrong, therefore your help is greatly
appreciated. Rob

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1

0
RobUCSD
6/5/2007 3:06:36 PM
access.formscoding 7493 articles. 0 followers. Follow

12 Replies
2424 Views

Similar Articles

[PageSpeed] 28

First of all, your existing code is incorrect: you're missing a space 
between the condition, and the ORDER BY clause.

If those are the only two options, try creating the following query, and 
using it for as the RowSource of your listbox:

SELECT * FROM tblRNnotesLU
WHERE fldRNnotesCode = 
Left(Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType, 1)
ORDER BY [fld.order]


-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"RobUCSD via AccessMonster.com" <u34500@uwe> wrote in message 
news:733fd524817a5@uwe...
> I'm trying to instert the following in the criteria section of a query in
> query builder; the itself is the record source for a list box
> *******************************************************************************************************
> If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = 
> "Ablation"
> Then
>
>    Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _
>      "WHERE fldRNnotesCode = 'A'" & "ORDER BY [fld.order]"
>
>         Else: If 
> Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType
> = "Device" Then
>
>                    Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU 
> "
> & _
>                         "WHERE fldRNnotesCode = 'D'" & "ORDER BY 
> [fld.order]
> "
>
>                End If
>           End If
> ****************************************************************************************************
> I started this by placing it in the onOpen event of the form, but then I
> realized I don't want to hard code this in the form. I'd rather to just be
> able to do it within a query. Then if I add new VisitTypes I can simply
> modify the query without touching the code in the form.
>
> I know my criteria statement is wrong, therefore your help is greatly
> appreciated. Rob
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1
> 


0
Douglas
6/5/2007 3:30:24 PM
Try this --
SELECT tblRNnotesLU.*
FROM tblRNnotesLU
WHERE (((tblRNnotesLU.fldRNnotesCode)=[Enter Code]))
ORDER BY tblRNnotesLU.Order;

-- 
KARL DEWEY
Build a little - Test a little


"RobUCSD via AccessMonster.com" wrote:

> I'm trying to instert the following in the criteria section of a query in
> query builder; the itself is the record source for a list box
> *******************************************************************************************************
> If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = "Ablation"
> Then
> 
>     Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _
>       "WHERE fldRNnotesCode = 'A'" & "ORDER BY [fld.order]"
>        
>          Else: If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType
> = "Device" Then
>     
>                     Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU "
> & _
>                          "WHERE fldRNnotesCode = 'D'" & "ORDER BY [fld.order]
> "
> 
>                 End If
>            End If
> ****************************************************************************************************
> I started this by placing it in the onOpen event of the form, but then I
> realized I don't want to hard code this in the form. I'd rather to just be
> able to do it within a query. Then if I add new VisitTypes I can simply
> modify the query without touching the code in the form. 
> 
> I know my criteria statement is wrong, therefore your help is greatly
> appreciated. Rob
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1
> 
> 
0
Utf
6/5/2007 3:31:01 PM
My Access 2002 SP3 will not let me create a field named   fld.order  so as to 
sort on.

-- 
KARL DEWEY
Build a little - Test a little


"Douglas J. Steele" wrote:

> First of all, your existing code is incorrect: you're missing a space 
> between the condition, and the ORDER BY clause.
> 
> If those are the only two options, try creating the following query, and 
> using it for as the RowSource of your listbox:
> 
> SELECT * FROM tblRNnotesLU
> WHERE fldRNnotesCode = 
> Left(Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType, 1)
> ORDER BY [fld.order]
> 
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> 
> "RobUCSD via AccessMonster.com" <u34500@uwe> wrote in message 
> news:733fd524817a5@uwe...
> > I'm trying to instert the following in the criteria section of a query in
> > query builder; the itself is the record source for a list box
> > *******************************************************************************************************
> > If Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = 
> > "Ablation"
> > Then
> >
> >    Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _
> >      "WHERE fldRNnotesCode = 'A'" & "ORDER BY [fld.order]"
> >
> >         Else: If 
> > Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType
> > = "Device" Then
> >
> >                    Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU 
> > "
> > & _
> >                         "WHERE fldRNnotesCode = 'D'" & "ORDER BY 
> > [fld.order]
> > "
> >
> >                End If
> >           End If
> > ****************************************************************************************************
> > I started this by placing it in the onOpen event of the form, but then I
> > realized I don't want to hard code this in the form. I'd rather to just be
> > able to do it within a query. Then if I add new VisitTypes I can simply
> > modify the query without touching the code in the form.
> >
> > I know my criteria statement is wrong, therefore your help is greatly
> > appreciated. Rob
> >
> > -- 
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1
> > 
> 
> 
> 
0
Utf
6/5/2007 3:52:00 PM
Thanks Doug, actually there are 5 different visit types and each type can
have more than one code, i.e., Ablation = A and E. I only wrote re: the 2 to
make things easier. Once I have the syntax down for one type I can do the
others. 

I'd rather do this in a query as there maybe future visit types. Then I can
alter the query and not the code. So back to my original post, can I place
the criteria, based on frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType
in the criteria of the field from my initial query that populates the list?
Hope this makes sense. Thanks, Rob
Douglas J. Steele wrote:
>First of all, your existing code is incorrect: you're missing a space 
>between the condition, and the ORDER BY clause.
>
>If those are the only two options, try creating the following query, and 
>using it for as the RowSource of your listbox:
>
>SELECT * FROM tblRNnotesLU
>WHERE fldRNnotesCode = 
>Left(Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType, 1)
>ORDER BY [fld.order]
>
>> I'm trying to instert the following in the criteria section of a query in
>> query builder; the itself is the record source for a list box
>[quoted text clipped - 27 lines]
>> I know my criteria statement is wrong, therefore your help is greatly
>> appreciated. Rob

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1

0
RobUCSD
6/5/2007 3:59:27 PM
Is this to go in the criteria of the underlying query. if so, it doesn't work.
I get "subquery can't return multiple records."

Again, I need to use If Then statements in a query's criteria. thank for your
help and I look forward to anymore suggestions. Thanks, rob

KARL DEWEY wrote:
>Try this --
>SELECT tblRNnotesLU.*
>FROM tblRNnotesLU
>WHERE (((tblRNnotesLU.fldRNnotesCode)=[Enter Code]))
>ORDER BY tblRNnotesLU.Order;
>
>> I'm trying to instert the following in the criteria section of a query in
>> query builder; the itself is the record source for a list box
>[quoted text clipped - 23 lines]
>> I know my criteria statement is wrong, therefore your help is greatly
>> appreciated. Rob

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1

0
RobUCSD
6/5/2007 4:42:39 PM
Ok Doug, here's my latest try. This goes in the criteria section in my query
qryRNnotesLU.fldRNnotesCode. Of course I get syntax errors. Could you please
help if you have a chance. Thanks, Rob

If (Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = "Ablation")
Then 
WHERE "fldRNnotesCode = 'A' AND 'E'"

Douglas J. Steele wrote:
>First of all, your existing code is incorrect: you're missing a space 
>between the condition, and the ORDER BY clause.
>
>If those are the only two options, try creating the following query, and 
>using it for as the RowSource of your listbox:
>
>SELECT * FROM tblRNnotesLU
>WHERE fldRNnotesCode = 
>Left(Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType, 1)
>ORDER BY [fld.order]
>
>> I'm trying to instert the following in the criteria section of a query in
>> query builder; the itself is the record source for a list box
>[quoted text clipped - 27 lines]
>> I know my criteria statement is wrong, therefore your help is greatly
>> appreciated. Rob

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1

0
RobUCSD
6/5/2007 5:24:33 PM
I'm not sure what you mean by "in the criteria section in my query".

Are you saying you've typed that into the criteria cell in the graphical 
query builder? It's not a valid If statement (there's no End If), but even 
if it were, you can't put VBA into a query like that. Additionally 
"fldRNnotesCode = 'A' AND 'E'" is invalid syntax (it would likely need to be 
"fldRNnotesCode = 'A' AND fldRNnotesCode = 'E'", but that won't return 
anything, since it's not possible for there to be a row which has two 
different values for the same field.)

How about explaining in words what you're trying to do? As you saw, your 
first attempt to describe was a little to abbreviated, since what I posted 
in response to the literal question apparently wasn't sufficient to meet all 
your needs.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"RobUCSD via AccessMonster.com" <u34500@uwe> wrote in message 
news:7341093bd09bc@uwe...
> Ok Doug, here's my latest try. This goes in the criteria section in my 
> query
> qryRNnotesLU.fldRNnotesCode. Of course I get syntax errors. Could you 
> please
> help if you have a chance. Thanks, Rob
>
> If (Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType = 
> "Ablation")
> Then
> WHERE "fldRNnotesCode = 'A' AND 'E'"
>
> Douglas J. Steele wrote:
>>First of all, your existing code is incorrect: you're missing a space
>>between the condition, and the ORDER BY clause.
>>
>>If those are the only two options, try creating the following query, and
>>using it for as the RowSource of your listbox:
>>
>>SELECT * FROM tblRNnotesLU
>>WHERE fldRNnotesCode =
>>Left(Forms!frmPtDemographicNew.frmVisitNewEdit.Form!fldVisitType, 1)
>>ORDER BY [fld.order]
>>
>>> I'm trying to instert the following in the criteria section of a query 
>>> in
>>> query builder; the itself is the record source for a list box
>>[quoted text clipped - 27 lines]
>>> I know my criteria statement is wrong, therefore your help is greatly
>>> appreciated. Rob
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1
> 


0
Douglas
6/5/2007 5:46:51 PM
Douglas J. Steele wrote:
>I'm not sure what you mean by "in the criteria section in my query".
>
>Are you saying you've typed that into the criteria cell in the graphical 
>query builder? 
Yes!
It's not a valid If statement (there's no End If), but even 
>if it were, you can't put VBA into a query like that. Additionally 
>"fldRNnotesCode = 'A' AND 'E'" is invalid syntax (it would likely need to be 
>"fldRNnotesCode = 'A' AND fldRNnotesCode = 'E'", but that won't return 
>anything, since it's not possible for there to be a row which has two 
>different values for the same field.)

Here's the sql statement as it was before I tried to insert the If statement
in the criteria cell of fldRNnotesCode:

SELECT tblRNnotesLookUp.fldRNnotesLUno, tblRNnotesLookUp.fldNote,
tblRNnotesLookUp.fldRNnotesCode, tblRNnotesLookUp.fldOrder
FROM tblRNnotesLookUp
WHERE (((tblRNnotesLookUp.fldRNnotesCode)="A" Or (tblRNnotesLookUp.
fldRNnotesCode)="E"))
ORDER BY tblRNnotesLookUp.fldOrder;

What I want is the query output to modified based on the value in the
fldVisitType. If the valueof fldVisitType =1, then the criteria for the for
fldRNnotesCode will be (((tblRNnotesLookUp.fldRNnotesCode)="A" Or
(tblRNnotesLookUp.fldRNnotesCode)="E"))

Hope that clarifies. Thanks for your help.

>How about explaining in words what you're trying to do? As you saw, your 
>first attempt to describe was a little to abbreviated, since what I posted 
>in response to the literal question apparently wasn't sufficient to meet all 
>your needs.
>
>> Ok Doug, here's my latest try. This goes in the criteria section in my 
>> query
>[quoted text clipped - 24 lines]
>>>> I know my criteria statement is wrong, therefore your help is greatly
>>>> appreciated. Rob

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1

0
RobUCSD
6/5/2007 6:15:37 PM
Since you need to map from one visit type value in the form field to 
multiple possible notes codes in the table you're querying, you'd likely be 
best off creating a table that maps between the two. (If it's a many-to-many 
relationhips between them, you'll need to introduce an intersection table to 
resolve that). You can then join that mapping table to your existing table, 
and then simply compare to the value on your form.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"RobUCSD via AccessMonster.com" <u34500@uwe> wrote in message 
news:73417b9552113@uwe...
> Douglas J. Steele wrote:
>>I'm not sure what you mean by "in the criteria section in my query".
>>
>>Are you saying you've typed that into the criteria cell in the graphical
>>query builder?
> Yes!
> It's not a valid If statement (there's no End If), but even
>>if it were, you can't put VBA into a query like that. Additionally
>>"fldRNnotesCode = 'A' AND 'E'" is invalid syntax (it would likely need to 
>>be
>>"fldRNnotesCode = 'A' AND fldRNnotesCode = 'E'", but that won't return
>>anything, since it's not possible for there to be a row which has two
>>different values for the same field.)
>
> Here's the sql statement as it was before I tried to insert the If 
> statement
> in the criteria cell of fldRNnotesCode:
>
> SELECT tblRNnotesLookUp.fldRNnotesLUno, tblRNnotesLookUp.fldNote,
> tblRNnotesLookUp.fldRNnotesCode, tblRNnotesLookUp.fldOrder
> FROM tblRNnotesLookUp
> WHERE (((tblRNnotesLookUp.fldRNnotesCode)="A" Or (tblRNnotesLookUp.
> fldRNnotesCode)="E"))
> ORDER BY tblRNnotesLookUp.fldOrder;
>
> What I want is the query output to modified based on the value in the
> fldVisitType. If the valueof fldVisitType =1, then the criteria for the 
> for
> fldRNnotesCode will be (((tblRNnotesLookUp.fldRNnotesCode)="A" Or
> (tblRNnotesLookUp.fldRNnotesCode)="E"))
>
> Hope that clarifies. Thanks for your help.
>
>>How about explaining in words what you're trying to do? As you saw, your
>>first attempt to describe was a little to abbreviated, since what I posted
>>in response to the literal question apparently wasn't sufficient to meet 
>>all
>>your needs.
>>
>>> Ok Doug, here's my latest try. This goes in the criteria section in my
>>> query
>>[quoted text clipped - 24 lines]
>>>>> I know my criteria statement is wrong, therefore your help is greatly
>>>>> appreciated. Rob
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1
> 


0
Douglas
6/5/2007 6:30:54 PM
Ok Doug, could you take on more crack at it. This is what I have in the
criteria cell now. 

If Forms![frmPtDemographicNew]![frmVisitNewEdit].Form![ReasonForVisitTest] =
"Ablation" 
WHERE=(((tblRNnotesLookUp.fldRNnotesCode)="A" Or (tblRNnotesLookUp.
fldRNnotesCode)="E"))
ORDER BY tblRNnotesLookUp.fldOrder;

I get a syntax error. I don't know how to do the transition between the If
Forms![frmPtDemographicNew]![frmVisitNewEdit].Form![ReasonForVisitTest] =
"Ablation"  and the Where statement

..Could you pls help. The previous query I posted using multiple criteria
works, that's why I believe it will work if I can just pluck the visit type
of the form.

Thanks, Rob
Douglas J. Steele wrote:
>Since you need to map from one visit type value in the form field to 
>multiple possible notes codes in the table you're querying, you'd likely be 
>best off creating a table that maps between the two. (If it's a many-to-many 
>relationhips between them, you'll need to introduce an intersection table to 
>resolve that). You can then join that mapping table to your existing table, 
>and then simply compare to the value on your form.
>
>>>I'm not sure what you mean by "in the criteria section in my query".
>>>
>[quoted text clipped - 39 lines]
>>>>>> I know my criteria statement is wrong, therefore your help is greatly
>>>>>> appreciated. Rob

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1

0
RobUCSD
6/5/2007 6:54:30 PM
I don't understand why you're so insistent for me to help you if you're 
going to ignore my advice!

As I wrote earlier, "you can't put VBA into a query like that"

Switch to SQL view: you'll see you've created a nightmare!

I don't see why you think it'll be any easier to maintain the hard coding in 
a query than it is in a form. The correct approach is to maintain the 
mappings in a table.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"RobUCSD via AccessMonster.com" <u34500@uwe> wrote in message 
news:7341d293aebd2@uwe...
> Ok Doug, could you take on more crack at it. This is what I have in the
> criteria cell now.
>
> If Forms![frmPtDemographicNew]![frmVisitNewEdit].Form![ReasonForVisitTest] 
> =
> "Ablation"
> WHERE=(((tblRNnotesLookUp.fldRNnotesCode)="A" Or (tblRNnotesLookUp.
> fldRNnotesCode)="E"))
> ORDER BY tblRNnotesLookUp.fldOrder;
>
> I get a syntax error. I don't know how to do the transition between the If
> Forms![frmPtDemographicNew]![frmVisitNewEdit].Form![ReasonForVisitTest] =
> "Ablation"  and the Where statement
>
> Could you pls help. The previous query I posted using multiple criteria
> works, that's why I believe it will work if I can just pluck the visit 
> type
> of the form.
>
> Thanks, Rob
> Douglas J. Steele wrote:
>>Since you need to map from one visit type value in the form field to
>>multiple possible notes codes in the table you're querying, you'd likely 
>>be
>>best off creating a table that maps between the two. (If it's a 
>>many-to-many
>>relationhips between them, you'll need to introduce an intersection table 
>>to
>>resolve that). You can then join that mapping table to your existing 
>>table,
>>and then simply compare to the value on your form.
>>
>>>>I'm not sure what you mean by "in the criteria section in my query".
>>>>
>>[quoted text clipped - 39 lines]
>>>>>>> I know my criteria statement is wrong, therefore your help is 
>>>>>>> greatly
>>>>>>> appreciated. Rob
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1
> 


0
Douglas
6/5/2007 8:58:12 PM
I'm sorry I have upset you. Not everybody is as keen as you are. Thanks for
your help.

Douglas J. Steele wrote:
>I don't understand why you're so insistent for me to help you if you're 
>going to ignore my advice!
>
>As I wrote earlier, "you can't put VBA into a query like that"
>
>Switch to SQL view: you'll see you've created a nightmare!
>
>I don't see why you think it'll be any easier to maintain the hard coding in 
>a query than it is in a form. The correct approach is to maintain the 
>mappings in a table.
>
>> Ok Doug, could you take on more crack at it. This is what I have in the
>> criteria cell now.
>[quoted text clipped - 33 lines]
>>>>>>>> greatly
>>>>>>>> appreciated. Rob

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1

0
RobUCSD
6/5/2007 9:09:18 PM
Reply:

Similar Artilces:

Contact with multiple Email Addresses
I have the Tracking device on in CRM 4.0, which is tracking all incoming and outgoing emails for leads, contacts and accounts. I am wondering if it is possible to add/track multiple email addresses for one contact. Is there anyway to associate more than one email address for an entity? Thanks, Geoff A contact (and also account and lead) has three e-mail fields. CRM uses all these fields for email tracking. -- Patrick Verbeeten (MCPD) CRM/.NET Consultant Aviva IT Tools for CRM Developers and Administrators http://www.patrickverbeeten.com/Tools.aspx "Geoff" wrote: >...

Fixed Asset Import Multiple Times?
Scenario: We have a new client on GP 9.0, SQL 2005. They are just starting to use Fixed Assets. We are planning on doing the initial import of fixed assets via Excel and the Fixed Asset Import Wizard. However, they will have many acquisitions in the next few months/years of additional companies (from which they will acquire many new fixed asets at a time). Question: Can we do additional imports (from excel and the FA import/export wizard) for new/additional fixed assets we acquire during these acquisitions? We would want to maintain all our current Fixed Assets, of course, and j...

Justify text across multiple columns
I have used Edit, Fill, Justify to fill rows with text within the boundaries of a set number of columns, but the text in each row is left justified. How do I justify the text (even spacing) in each row. If I highlight all of the text (multiple columns and rows) and then select Format, Cells, Alignment, Justify; Excel jams all the text into the first column. I know I could use a text box, but I'd prefer not to. Any help would be appreciated. Thanks. -Dave Hi Dave, Select the cells you want to center on. Under Format > Alignment > Horizontal drop down > Center across se...

Order in query is lost in report
I have a subreport which is based on a query. When I run the query it delivers the fields ordered by date descending, as requested. When I run the (sub)report, all fields are delivered but in date ascending. Any clues or do I need to post more specifics? Thank you, Jon Use Sorting & Grouping in your report at design time. There is a button on your menu bar that will present you with a dialog similar to setting indexes on a table. Steve -- Steve McGuire MCSD, MCAD, MCP "Jon Geiger" wrote: > I have a subreport which is based on a query. When I run the query it &g...

if then statement with a vlookup?
Hello, I am trying to come up some code to search a workbook for numbers and then put them in a column on the last page but if the number already exists, do nothing. I need it to do this everytime I run the macro. Any ideas? here is an example: look for "day" if true then i want the number in column j on that page to go to the last page in column k, but if the number is already there then do nothing. if it is not there then the next cell down. Does this make any sense? I think I confused myself. Any help is appreciated Thank you A little clarification ple...

Multiple Versions of Office Apps
Version: v.X Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I have not upgraded to Office 2008 because the budgeting programs I use in Excel require macros. I do, however, want to upgrade Entourage for better Exchange compatibility. If I install Office 2008, can I still keep my Office X version of Excel on the same system (PowerBook G4)? Will there be conflicts? Thanks, -Jd On 3/25/08 9:04 AM, in article ee95f9d.-1@webcrossing.caR9absDaxw, "jdbrewer@officeformac.com" <jdbrewer@officeformac.com> wrote: > Version: v.X > Operating System: Mac OS X 10.4 (Tig...

Update Query basic
I have Table1 with field Track, I want to update with info from field Track in Table2 add both tables to design view field track but don't know what to put in update too. Am missing something here - hope you can help. Want to eventually use this to update adress fields from one table to another with EmpID connecting each. UPDATE table2 INNER JOIN table1 ON table2.ItemID= table1.itemID SET table2.UnitPrice = table1.UnitPrice would update the unit price of table2 with the unit price coming from table1, for the same itemID (ie, the APPLE unit price won't be updated by the ORANGE ...

query tables
i have inherited a database w/ o documentation & noone to tell me its history. I have a query that shows 2 tables: "A" and "B" Table A has several fields, B only 2. The tables are joined on these 2 fields. 3 fields called for the query, all from table a. When i run the query i get data. But where is this data coming from? I can find no Table A or B; i can find no query A or B. I am stumped. Can anyone help me? Could be a few things. For example A and B are queries and not tables. Whoops! You already eliminated that. Make sure to scroll left and right on the d...

using MS Query with iSeries ODBC and special caracter
I need help. I found problems using MS Query with iSeries ODBC driver when there are table o library named with special caracters ("$" or "_") ...

Difficult query?
Dear friends, I have a database in Access 2003 about Forest Inventory. I have data collected from sample plots in a table (T_RawData1991-2001 – linked from T_SamplePlotsDetails one to many). For each tree I have its SerialNo recorded manually, so in a sample plot I may have 5 trees (up to 100), serially 1-5. I have also recorded the distance and the azimuth of each tree from the center of the sample plot. Now, I want to have (using a query perhaps) the following results: The distance combination between all Trees e.g. 1-2, 1-3, 1-4, 1-5, 2-1, 2-3, 2-4, 2-5, 3-1, 3-2, 3-4, 3-5, 4-1...

Setting a conditional value in a query to perform an additional qu
Ok, I have a slight brain twister here and wondering if anyone could help shed some light on this. :) Im in the midst of creating a database that will hopefully replace a paper-based system of storing 'shift runsheets' from a 24/7 office. There is 3 shifts per 24 hours; Morning (6am-2:30pm), Afternoon (2pm-10:30pm) and Night (10pm to 6:30am). Each day it adds a new row of data to the table "shift" and users can peform searches on previous sheets by searching the table "previous" which links to "shift". When a user wishes to search, they select the ...

If Statement with Two Conditions
I'm trying to do an if statement with two conditions like the following If a value falls between two dates then give it a value of 1, if not give it a value of 0. The if statement works well with one condition but when I try to do something like this = if (a2<=b2<=c2,1,0) it does not work. Help Leroy Leroy, Try something like =IF(AND(A2<=B2,B2<=C2),1,0) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Leroy" <anonymous@discussions.microsoft.com> wrote in message news:EF57802D-CA6F-4A45-95FB-9807FFEA1...

Multiple Worksheets
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) How can I copy same info between worksheets? You need to be more specific about what you're trying to accomplish... For what little you've disclosed regular copy/paste will work, but storing duplicate copies of the same data on multiple sheets [or in multiple workbooks] is redundant & inefficient. I'm sure you're clear in your own mind what you're trying to do, but these one-liners do not effectively convey what you're thinking :-) Also, it's fine to post multiple questions & use sepa...

QUERY: export script
I am currently using Outlook Express to handle my POP3 mail however I sometimes use Outlook too! There are times when I want to export certain mail in a batch and save them. Is it possible writing a script for Outlook Express to batch export mail and save them each with a filename format of (YYMMDD_HHMM, year, month, day, hour, minute - date-timestamp of the email ofcourse)?? If it is not possible under Outlook Express, is it possible under Outlook? I have programmed Excel VBA before, so how easy would it be to program for Outlook or Outlook Express? I have Outlook 97 but never seen an...

Criteria Question
*I'm not very knowledgeable with Access* I want to input a criteria for a query with a range of results, some are all numerical and some contain letters. How do I create a range criteria for example "11111 to 33333" or "H12345 to H23456"? The only way i have been able to do it is very confusing, for instance I use Like "[1-3][1-3][1-3][1-3][1-3]", which can produce the results but I'm not sure if there is a better input strategy. You can use Between with Criteria: Between 11111 and 33333 (if a numeric field) Between "H12345" and "...

How can I host multiple domain with single EX2003?
Dear all, As titled... Should I configure the server to bind another IP address to host another domain??? If yes, it's undoubtable to waste IP address.... Please help, thanks.... You mean multiple SMTP domains? If so, check out this article, which applies to E2k3 as well: http://www.msexchange.org/tutorials/MF010.html -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchange.co.uk "Keith Ko" <Unavailable@127.in-addr.arpa> wrote in message news:uB3Yl9w5DHA.360@TK2MSFTNGP12.phx.gbl... > Dear all, As titled... > > Should ...

Sum of counted values in a query
Hello, My question,.... I have a query which count the number of records in a table (as a result of the menuoption view-> totals ->count) as a result of the group by function for unique records. The result is: column1 column2 A 10 B 15 C 8 Now I want to insert a column3 which gives the sum of that counted values. I'd tried something like: expr1:Sum(Count[column2]) The result should be for each record in column3: 33 (result of the sum 10+15+8) It doesn't work. Somebody knows a solution ? Thanks regards, Johan You can always writ...

IF statements #5
In column A there are either values of "JR.", "CPA", "MD", or nothing. Can I create an IF statement that says if there is a text value in the cell put parentheses around that value and if there is nothing in the cell do nothing? Any help would be appreciated =IF(A1<>"","(" & A1 &")","") entered in B1 and copied down. Gord Dibben MS Excel MVP On Thu, 28 Jun 2007 09:46:03 -0700, Excel Function Application <ExcelFunctionApplication@discussions.microsoft.com> wrote: >In column A there are eithe...

Extract multiple cabs
How can I extract msp files from cab files with VBS? I have 99 cabs. Each cab may have at least 1 or more msp files. I'm trying to edit an older script I used to qchain updates years ago, but getting "Expected end of statement" errors. That script is such a mess now, it's not even readable or worth posting. Any help or suggestions is greatly appreciated. Thanks -Steve --- "stev379" <stev379@discussions.microsoft.com> wrote in message news:CE14AAF5-606E-4299-A32C-FB1909AE34C2@microsoft.com... > How can I extract msp files from ca...

Nested if statements Arrays
I basically have a collum of dates and I want to have a descriptive cell with the range of dates. The dates are all entered from another sheet by a user and this page is a summary page the formula looks something like: =A25&"-"&IF(AND(A26=0,A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A34=0,A3 5=0,A36=0,A37=0),A25,IF(AND(A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3 4=0,A35=0,A36=0,A37=0),A26,IF(AND(A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3 4=0,A35=0,A36=0,A37=0),A27 with A25 being the first date and the sequence of IF statements determining what the end date should be. This works...

How to round number to the nearest multiple of signifiance?
I have entered the formula in my cell, and I want my result to be rounded to the nearest multiple of signifiance (for example 5). I have tried with function CEILING and FLOOR, but these functions only rounds number UP(ceiling) or DOWN(floor), so for this I have to know what my result is to rounded it. That is not the solution, I would like for Excel to do it automaticaly. Here is example: If my result is say 117.49 and lower (to the limit of 115), I want to round it to 115, if result is 117.51 and higher (to the limit of 120) to round to 120...(multiple of significance is 5). Can it be done...

POS (Not RMS) Database Query Question
Our owner would like a automated text message sent to his phone every 30-60 minutes with total daily sales. If we create a query and have it run every 60 minutes, and the query results sent via SQL Mail, that should do the trick. What are the tables housing current daily sales totals? Mark Kalmus wrote: > Our owner would like a automated text message sent to his phone every 30-60 > minutes with total daily sales. > > If we create a query and have it run every 60 minutes, and the query results > sent via SQL Mail, that should do the trick. > > What are the table...

Passthrough Query
I have a form in a project DB that its source is a view (csp_UserNames) that accepts parameters,but I would like to pass a parameter that the user types in the form itself (USRNAME field) and then requery the form. If I put the following in its source it works; Exec csp_UserNames This returns all values in the users table If I put: Exec csp_UserNames alopez It returns user angel lopez (the login ID is alopez for this user) But I would like to pass the value in my form (Login_frm) field (USRNAME) to the parameter so that when I type jsmith in the USRNAME field (afterupdate) I would just do...

Backup Database Queries
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I have several backup copies of my Entourage Database kept as Encrypted Zip files using PGP. <br><br>I recently inadvertently deleted my tasks and want to use the backup Database to get them back! <br><br>On Decrypting my backup it shows as Kind Document; how do I change the extension so that it shows in Finder as a Entourage Database? <br><br>Are there any suggestions about how I can then open the Database, Export Tasks and then re-open the current Dat...

Returning Multiple values from A Vlookup
I need a formula to return multiple values from a look up table as the number appears multiple times in a column. Reference cell is $C$6 eg = 4101 Current formula only return the first one it finds =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP($C$6,TNI,3,FALSE))) I need the Cell to return all the values it finds from the one code eg = QCBD, QTNS/QCBD/QBMH/QMRE, QCBD/QAGW Lookup table is TNI (A1:C1053) I need it to return all values in Column (C) A B C 4077 All suburbs QRLE/QRLD 4078 Forest Lake QRLD/QLGH 4101 H...