Topics Search

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

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
Sponsored Links:

Similar posts...

 

Possible to link fields in a form to narrow selections?

Possible to link fields in a form to narrow selections? Icon
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.
 

query w/ filter, but display all records

query w/ filter, but display all records Icon
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
 

validation rule (for a record in a table) with dlookup

validation rule (for a record in a table) with dlookup Icon
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

Multiple records on a single page Icon
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
 

automatically adding fields in a request

automatically adding fields in a request Icon
SELECT table.field1, table.field2 .

but this means that field1, field2 field"n" .are known, how about if new fields are added to the table (for instance new product references)

would it be possible to write the SQL in such a way that the fields would be automatically considered without having to change the request. Possibly using a matrix of field names.
 

Crushing Rows

Crushing Rows Icon
I have a table like this:
Field1 Field2
Alfa 1
Beta 2
(Blank) (Blank)
Delta 4
etc.

I need a Visual Basic way to convert the table like:
Field1 Field2
Alfa 1
Beta 2
Delta 4
etc.

In other words, using VB, I need to delete the blank rows
 

Converting text to numbers for limited rows

Converting text to numbers for limited rows Icon
I have a table in SQL Server, which has two columns one of which contains

* numbers as characters (eg. 214.67)
* the letter U
* NULL

I need to populate the second column with

* conversion of character numbers to numbers
* zero if the other field contains the letter U
* 999,999 if the other field is NULL

I can select only those records which contain numbers as characters

select field1 from Table
where
(field1 is not null
and field1 'U')

however when I use this in the update I get an error.

UPDATE Table
SET Field2 = val(Field1)
where
(field1 is not null and field1 'U')
 

distinct(mid()) doesn't work

distinct(mid()) doesn't work Icon
select field1, distinct(mid(field2,7,3)) from table1

if I try this query, it shows undefined function. if I just try distinct(field2,7,3), it works.
 

How to Add Values to A field in a Table

How to Add Values to A field in a Table Icon
I have a huge database size now and I want to Archive it to another Access Database. I Linked the Archive Table to my Main Access Database. User must name their archived respective. For example:

From: Main Access Database, I have Field1, Field2, Field3 and in my Archive Database I have YEAR, Field1, Field2, Field3

I created a button to append the data to the Archive Database, in my archive table should be:
 

multi data in one cell to multil rows

multi data in one cell to multil rows Icon
Here is what I got

ID field1 field2 field3 field4 filed5
1 OH, NJ, IL john a,x,c,d,g $1,$2,$5 filepath
2 NJ, CA Pam z,d $21, $5 filepath

as you see some field have multiple data on them, want to for each one of the data get new row, where the unchnaged data is copied to and there is only on data per cell for changed data something like below

ID field1 field2 field3 field4 filed5
2 NJ Pam z $21 filepath
2 CA pam d $5 filepath

does anyone know any method or VBA that can accompolish this