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

Views: 27
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

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])