Related Topics

Insert Query With Condition

Catching record id in a form
I have a INSERT INTO query to upload and download a table between a local access table and a online sql server table and a UPDATE query to do the same. I have this connected to a button in a form.

My issue is instead of doing the INSERT INTO and UPDATE queries for all the records in the table I would like to have a WHERE condition for my current record ID in my form (the ID being the primary key), so only the one record in a form is altered and passed between the local access table and the online sql server table.

So how do I catch the record ID of one row in the form and pass it as an arguement for the WHERE condition in my INSERT INTO and UPDATE queries. I want to do this via a button in the form


Macro If Condition.
I want to run a Query-B based on condition of another Query-A in Macros or VBA Code.

Query-A | Select name from employee where inserted_date= date() //today's date

Query-B -- bla bla

My Need is if Query-A returns any values, Query B need to be executed. I Tried with IF CONDITION in macros but its saying table name not found.


LIKE Condition (using wildcards) in Access 2007
The LIKE condition allows you to use wildcards in the where clause of an SQL statement in in Access 2007. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.


INSERT with SELECT query.
I have a SELECT query whose results I need to INSERT into another table. Currently I have made the query an Insert query but this makes testing and development difficult. Most times I just need to browse the records returned without inserting them as they already exist in the target table. I have kept a separate copy of the query as SELECT but keeping them both updated is also demanding. Is it possible to use an INSERT statement with Currentdb.Execute and pass the SELECT query as an argument/parameter instead of individual records?


INSERT with SELECT query.
I have a SELECT query whose results I need to INSERT into another table. Currently I have made the query an Insert query but this makes testing and development difficult. Most times I just need to browse the records returned without inserting them as they already exist in the target table.

I have kept a separate copy of the query as SELECT but keeping them both updated is also demanding.

Is it possible to use an INSERT statement with Currentdb.Execute and pass the SELECT query as an argument/parameter instead of individual records?


Print Question
I have a macro that generates a report based on a query with a date range condition and a field value condition of -1. When the macro runs and both conditions are met I get what I want. However when the -1 condition fails, or is 0, I still get the report with #error which is to be expected.

I have tried using the where conditon field in the macro and set it so the field value is equal to -1 but I still get the report with the #Error.

Is there a way to not have the report print when the second condition is not -1?


Query criteria in linked table
I'm relatively new to the Access game but amd trying to extract some data from a healthcare database that I have created.

There is the main table with standard/simple data which has a linked table that allows multiple values to be entered. I have created queries that will count how often each individual response has been entered however I want to create a query that tells me how many records have more than one (multi)value selected.

This would mean that in our reporting we could say x% of people have this condition, x% of people have this condition, x% of people have this condition while x% of all respondents had more than one condition.

NB. The linked multivalue table has approx 20possible responses while the main table has about 1000 records


Subform query code
on one of the textboxes on my SUBFORM I put a condition on the "on lost focus" event that if the data entered on the textbox is already stored on my table it will open a new form showing the previous record of which it has the same value. my problem is I don't know how to create a query with a condition from a subform. this is the condition of the query I put on my criteria of my query:

field = [Form].[Form Name].[field value]

it doesn't work tough, it'll ask for the value to be entered manually. how do I code the query if the field value is from a SUBFORM


Filtering a Query
I have a Query with a calculated filed. The field is holding the value of either "1" or "2" based on the iff statment I created. can someone help me with how to filder this query to show me only the records witha value of "1" and then save the filter. I have tried the "filter by selection" but when i get out of the query and then go back in the query is unfiltered again.

If I try and write a condition in the "condition" field then when I run the query it wants to prompt me for input.


Append Query - Insert Unique Records only
I am trying to make an Append Query for insertion of data from one table to another, Is it possible to insert only the unique data without any criteria like chkbox=true etc (I mean only insert unique records).

I have noticed that every time when you run append query it can insert duplicate of record?


VBA Loop through, check condition, replace
I want to write a vba code to loop through a column in my table and check for a condition, and where it does not equal that condition I want it to display the whole row in a message box and ask to amend the field to meet that condition. If I say Yes I want the amendment to proceed.

For example, the fields would be mostly say 31/08/2012 but one entry may be 30/07/2012. I want the code to pick this out and suggest whether I want to change this to 31/08/2012.


Insert Multiple Records into a Table
Can I insert multiple records into a table using "INSERT INTO"., it seems it can insert only one record at one time.

INSERT INTO Table1 (F1, F2, F3, F4) VALUES ( 'CCP', 0.0115, 0.008202, 120)

Is there an alternate way to insert multiple records in MS Access Table using a query.


UPDATE and INSERT
I'm creating a ms access application where in update records if ID is already existing otherwise insert into table if not.

i've use IF EXISTS but I got error from my query. it says that expected UPDATE,SELECT, INSERT, DELETE into expression.

how to do this in single query? looking forward on response.


Create a query whose LIKE clause gets its condition from a text box
In Access 2003/XP/2000/97, how can I set up a query so that the LIKE clause gets its condition from a text box on my form?


Get Value from query in a form unbound field without where condition
I have a query which includes 3 fields:
(1) - Sum of WithDrawalAmount
(2) - Sum of DepositAmount
(3) - BalanceAmount (Balance: [Sum Of Deposit Amount]-[Sum Of W/D Amount])

I have a bound Form including a unbound field name "GrandTotal". I want to show query "BalanceAmount" value result in the Form GrandTotal field. how it is possible.
DLookup and ELookup funcations need the give where condition but i want to show the value without where condition.


Query criteria in linked table
I'm relatively new to the Access game but amd trying to extract some data from a healthcare database that I have created.

There is the main table with standard/simple data which has a linked table that allows multiple values to be entered. I have created queries that will count how often each individual response has been entered however I want to create a query that tells me how many records have more than one (multi)value selected.

This would mean that in our reporting we could say x% of people have this condition, x% of people have this condition while x% of respondents had more than one condition.

NB. The linked multivalue table has approx 20possible responses while the main table has about 1000 records.


Where Condition - form error
This database is set up so that before any forms are opened on the main page, a Family must be searched/selected first. I had used the Where Condition ="[FuID]=" & [FuID] so that when the form opens, records for that particular family are filtered. This works great. but if there is no family selected and you try to open one of the forms, an error message appears. " Syntax error (missing operator) in query expression '[FuID]='. How do I still use the above where condition, but also ask, if there is no FuID selected, open the form blank? I am not familiar with code and was fortunate to have someone help with the Where Condition I am currently using


How to verify Insert Operation
I am using an insert query how can I verify it programetically without looking into the table.

e.g db.execute("Insert into xyz values (a,b,c,d);")

is there any true or false or any other indicator or anything elase which verify the insert operation


Insert Into
so I have a query that will insert into a table some values.
It goes as follows
INSERT INTO TblStockFinal (StockLevels)
SELECT (SUM(QrySales1.SalesByHalfPintBottleorCan))
FROM QrySales1
WHERE QrySales1.ProductID=1 OR QrySales1ProductID=2;
but this is not working.


Create an additional column in query/table?
Can I create an additional column in Access, based on certain condition such as if condition = True,

than Total amount | 200?

How could I write that in my VBA?