Question on Or Statement in Query

[sokoye1]

Hello all,

I'm trying to run this query:

Select CRSDesc, Category, CrsDur
From [Fitness Course]
Where (CrsNum = [Enter a Course Number] or CRSName = [Enter a Course Name]);

I want to make it so that once I enter a Course Number, if that response is correct then it doesn't ask me to enter a course name next.

OR


How would I make it so that I could enter a Course Number OR Course Name in one statement and have it return the given information.

 

[plog]

Whenever you include a field not in the FROM clause of your query, it's going to ask. You have included 2 such fields, thus 2 prompts. My suggestion would either make a form that uses VBA to implement your logic, or change your SQL so that it only has 1 unknown field:

Select CRSDesc, Category, CrsDur
From [Fitness Course]
Where (CrsNum = [Enter a Course Number/Name] or CRSName = [Enter a Course Number/ Name]);

Naming them the same thing should prevent it from asking twice.

 

[sokoye1]

Whenever you include a field not in the FROM clause of your query, it's going to ask. You have included 2 such fields, thus 2 prompts. My suggestion would either make a form that uses VBA to implement your logic, or change your SQL so that it only has 1 unknown field:

Select CRSDesc, Category, CrsDur
From [Fitness Course]
Where (CrsNum = [Enter a Course Number/Name] or CRSName = [Enter a Course Number/ Name]);

Naming them the same thing should prevent it from asking twice.

Ah, that makes a lot sense. Sorry for such a simple question, I'm a bit new to Access. Thank you so much for the response!!

 

[namliam]

If you are up for the challange you want to do a "search by form" instead of these ugly default popups.... aside for looks this kind off solution will run you into trouble down the road if you keep using it.

Checkout this thread and see if you can figure out what to do.

Also do some research into naming conventions, cant find the thread offhand but put some time into finding it and/or researching it for your tables (as far as I can see for now) in particular.

 

[sokoye1]

If you are up for the challange you want to do a "search by form" instead of these ugly default popups.... aside for looks this kind off solution will run you into trouble down the road if you keep using it.

Checkout this thread and see if you can figure out what to do.

Also do some research into naming conventions, cant find the thread offhand but put some time into finding it and/or researching it for your tables (as far as I can see for now) in particular.

I will try to do this. I really appreciate the help.

I have a question on another topic if you can help me:

Update Item Set QuantityOnHand =
(Select Item.QuantityonHand - Transaction.QTYPurchase
From Transaction, [Transaction Item], Item
Where Transaction.TransNum = [Transaction Item].TransNum
And Item.ItemID = [Transaction Item].ItemID)
Where item.ItemID=
(Select Item.ItemID
from Item, [Transaction Item]
where Item.ItemID = [Transaction Item].ItemID);

I am trying to run this query, but I keep getting an error message stating that "operation must use an updateable query" I'm not really sure why what's being updated isn't clear, but if you could help me on this it would be greatly appreciated.

 

[namliam]

Your join constructs are not "access" they are "Oracle" joins.... that may be part of the issue...

Update statements are notoriously complex with this kind of thing, try creating seperate queries before using them inside the update.