IF field1 = x and field2 = (y or z) then ...

Is there a way to use the IF , Then function this way?

I have these fields I am Querying

SID
CASENUMBER

I want to do something like this.

IF SID = "X999" AND CASENUMBER = "1234567890" or "0987654321" or "1122343234" THEN.

Is there a way to group the case numbers together so I don't have to write it out like.

IF SID = "X999" AND CASENUMBER = "1234567890" OR SID = "x999" AND CASENUMBER = "0987654321" OR SID = "X999" AND CASENUMBER = "." ?

Because I have like a gazillion case numbers and only about 25 SID's

Sponsored Links:

Related Topics

ORDER BY ... If (SQL syntax question)
I have the following as the Row Source for a list box:

SELECT Field1, Field2 ORDER BY Field1, Field2;

Supposing the possible values for Field1 are 1 and 2, how do I get Field2 to sort in ascending order if the value in Field1 is 1 but in descending order if the value in Field1 is 2? I'm guessing it's something like this, but I don't know enough about SQL to work out the correct syntax:

SELECT Field1, Field2 ORDER BY Field1, If(Field1=1, Field2 ASC, Field2 DESC


Sum of field1 where field2 is true
Field1 contains a number
Field2 contains a checkbox (true/false)

I have a textbox in the report saying (Control Source) =Sum([Field1]) to, of course give me the sum of all of that field.

I am trying to get another textbox to sum the records in Field1 for all the ones in Field2 that are checked (True).

It seems I can do this in the Control Source with an expression but I have no clue how to get there. Something like:

=Sum([Field1]) Like ([Field2]) = True

Am I barking up the right tree?

I hope my information is sufficient


Update not overwrite
Can you please give me the code on which I can just update an existing entry in a table?
Suppose I have a table that has field names, field1, field2, field3. field1 and field2 has already a value and field3 = Null (im talking about the value in the database)
what I want to happen is to have a form to be filled up to update a value in field3 without affecting field1 and field2. I hope I made it clear.


DateDiff returning negative
Here's what I'm trying to do.

HoursRan: DateDiff("h",[Field1],[Field2])

Both fields are date/time that cover a 24 hr period,
for eample some records equal field1=12:00am, field2=3:00am which = 3, these are ok, but alot of times they equal field1=3:00am, field2=12:00am these are the ones I'm having a problem. instead of equaling 21 they are equaling to -3.
Field1 is a start time that always starts at 12:00am on current day
Field2 is an end time that ends every morning at 12:00am on next day


Code to split CSV file fields and put in to a table
I am trying to create a module in VBA within Access 2010 that when run, will open up a specific .csv file, and will split values up according to a spec I give (where to start, length of field etc) and put the information then in to an Access table.

E.g. I have a file that looks like:

Field1 Field2 Field3 Field4 Field5 Field6
Field1 Field2 Field3 Field4 Field5 Field6
Field1 Field2 Field3 Field4 Field5 Field6
Field1 Field2 Field3 Field4 Field5 Field6
Field1 Field2 Field3 Field4 Field5 Field6

Can anybody advise me on how I can achieve this task please as I am completely puzzled


Possible to link fields in a form to narrow selections?
I'm working on making a form more user friendly -
What I'd like to do is to link multiple fields and to narrow selections in field2 and field3 based off of what a user selects in field1.

Example:
I have field1 Principal with 10 options.
I have field2 PrimaryCategory with 20 options, however, there are only 2 valid options per Principal.
I have field3 Brand with 50 options, however, there are only 2-5 valid options per Principal.
Right now, all options for field2 and field3 show up in the combo box regardless of what is chosen in field1.
What I'd like is once user makes selection in field1, for field2 and field3 to automatically show ONLY the valid options for that specific selection in field1.

Would an ifthen statement work here - and if so, do I put that in the code on field2 and field3?

I'm working in both Access 2003 and 2007, depending on if I'm doing the work at the office or at home.


Conditional Formula?
I am developing a simple single database. I have two to three fiels that I want to relate (maybe not?). Here it is. field1 is a drop down list of departments and field2 is a drop down list of equipment. What I would like to be able to do is to select a department from field1 and have field2 provide (auto) a list of equipment that are only available for the department I selected on field1.


query w/ filter, but display all records
My DB table:
FIELD1 FIELD2 other fields
1 FY11 other-values
1 FY12 other-value
2 FY12 other-value
3 FY11 other-value

display results:
FIELD1 FIELD2 other fields
1 FY11 values
2 FY11 N/A
3 FY11 values

for the records that don't satisfy filter condition, display its FIELD1 & FIELD2 value, but set the rest to N/A, instead of skipping the entire record from the result display.
Please show me how to achieve this algorithm:
For each value of FIELD1, do this: if FIELD2 is FY11, print the rest of this record, else print N/A for the rest of this record

Please show me how to do this using any ACCESS mechanism


Selecting only blank fields in access 2007
Lets say I had a table like below, and I only want to select the name and any field which was blank / null
name,
field1,
field2,
field3,
.....
For example, if the name was John and field1, 2, and 6 were blank I would want my results to be
Name Field1 Field2 Field6
John null null null


validation rule (for a record in a table) with dlookup
In a table with a double primary key, I want, to check it doesn't exist.

To solve this problem, I presume, using a validation rule is the best option. Assuming that Field1 and Field2 are both primary keys and the combination of both keys must be unique, what's the best validation rule?

Can something like as follows work?
Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2])


Multiple records on a single page
Currently I have a report that formats like this:
ID 1
Field1 DataA
Field2 DataB
Field3 DataC

---Page Break---

ID 2
Field1 DataE
Field2 DataF
Field3 DataG

I would like it to format like this:
ID 1 2
Field1 DataA DataE
Field2 DataB DataF
Field3 DataC DataG

Does anybody know how to achieve this in a report, I can't find information on it anywhere and I've been searching for days now


Update field2
I have a table called DATA, field1 is populated from an import. what I want to do is populate field2 with data. the datain field1 starts with a 4500* and all 100* numbers below relate to the 4500* above it. I need to put the 4500* number at the side of the 100* it relates too. here is an example, ....


Can access use the SQL SUM function?
This issue has been resolved elsewhere, but thanks go to plog for his help and insight.

I'm trying to run a query as a rowsource in a combo box as

SELECT Table.[Field1], Sum(Table.Field2) AS Total_Sum FROM Table WHERE (((Table.[Field3])=[Number]));

If I remove 'sum()' then the box shows the first results from Field2, but as soon as I add 'sum()' it returns Field1 instead, I assume this is because Field2 returns null.


How to set values in different records?
I work with Access 2003 and I am new in VBA.
I would like some help with the following:
In a query that is sorted by field1 (this is the primary key)
I want to write this:

If field2.record(m)=field2.record (n) Then field3.record (p)="Yes" Else field3.record (p)="No"

field2 and field3 are texts
fields1 is a number
(m), (n), (p) are the numbers of records by which we must move from the first record in this query, if it sorted by field1


button for double field
I make form and insert into some fields. how make button, that you add double fields under create fields?

I meen so:

I have these fields:

field1 field2 field3 field4 // pres button for double fields

field1 field2 field3 field4
field1 field2 field3 field


Distinct Rows with Max Date
I have a table which is mentioned below.

Branch
TxnDate
Field1
Field2
Field3

B1

B1

B2

B2

B3

B3

I need to build a SQL statement which the result should be like below

Branch
TxnDate
Field1
Field2
Field3

B1

B2

B3

The logic of this result is
1. The Branch name should be unique
2. TxnDate is the Max(TxnDate) of each branch
3. Field1, 2, 3 values should be the corresponding values of Max(TxnDate).

Kindly help me. I have attached the database along with this


moving data
I have a little issues. How could I move one record from one table to another table when a criteria is met. example.

Table1 : has three fields. serial , field1, feild2,

Table2: serial , field1, feild2,

Then a form that user could pull up or enter the info of serial , field1, feild2. Mind you that Field1 and Two are entered in stages .

I need to make it so that when serial and field1 and field2 are true. Then Have the DB somehow move that record to Table2.


Populate table entries when clicked on form button
I have a MS access 2007 form with two fields say Field1 and Field2 and a button. When button is pressed I want to populate all the entries matching Field1 and Field2 in datasheet view from table Table1.

For this, I have created a sql query using simply SQL query wizard and got a sql query "SELECT Table1.entry1, Table1.entry2, Table1.entry3 Table1.entry4 FROM Table1;".
but I want to populate only the entries that matches Table1.entry1 = Field1 (of Form) and Table1.entry2 = Field2 (of form). I'm sure that I need to use the where clause for this.

But I dont know how to retrieve the form values inside a sql query. Please let me know whether m going in the right direction or is there any other way to do this


Fill in Null values
I have a problem with an Access table, I simplified the issue in the below attached zip file

There is a field that looks like this:

Field1
a

b

e

and the final results should look like this

Field1 Field2
a a
a
b b
b
e e
e

I tried using dlookup and dsum in a query but I did not succeed


why am i getting #Num!
Can anyone tell me what I need to add/modify to get the below given ratio as a percent This code works fine when I have a proper numerator and denominator. When it comes to 0/0, I am getting the result as #Num! instead of which, it should be displaying 0

=(Count([Field1])-Count(IIf([Field1]="N/A",0)))/((Count([Field1])-Count(IIf([Field1]="N/A",0)))+(Count(IIf([Field2]="Y",0)+IIf([Field1]="N/A",0))))