hi. i are replacing queries in my tables to get the database ready to convert to SQLServer. Am having a problem replacing a query that is brings in a table for a drop down and inserting the selection in the record. during creation, the record shows a date, id, task, hours. the datatable needs date, id, task_code, task_name, task_job, hours. On "task" the drop-down allows for selection of several choices that are 3 columns wide. i can get the columns to show but only the first column currently goes in my new table. the table's columns that the selection comes from is "task_code,task_name, task_job". All columns are needed as different areas use the same task code but use different jobs or names.
I was told to use a insert into statement, however am having problems with it.
have as follows: SQLStatement = "SELECT [Task_code], [Job_name], [Task_name] FROM tablea; " rec.Open SQLStatement, , adOpenDynamic
Can I use the append query to update a table by replacing all existing information with updated information while at the same time adding all data that isn't currently in the table?
Right now every time I run the Append Query it just adds the same information as a new row instead of replacing the existing row with the updated information.
In my forename field I have entries with just the initial. I would like my query to filter out anything less than 2 characters. Does anyone know the criteria format?
How to get the last character of the string in query?
I have a table name PlateNo and I want to get the last character for registration purposes.
Ex. ZMD-123 - I want to get the number three(3) and if its possible every time the user types the last digit in criteria it will show all plate number ending in the entered number.
I've got a query that does exactly what this (http://www.access-programmers.co.uk/forums/showthread.php?t=89564&highlight=null+records) chap got his to do.
However I want to add a "*" character to the criteria to allow for searches with partial matches. Here's the criteria that works:
[title]=[Forms].[Search]![Title] Or [Forms].[Search]![Title] Is Null
However when I try the logical extension:
[title]=[Forms].[Search]![Title] & "*" Or [Forms].[Search]![Title] Is Null
this doesn't work, and nor does:
[title]=([Forms].[Search]![Title] & "*") Or [Forms].[Search]![Title] Is Null
WHAT DO I DO? I'M TIRED AND I CANT THINK STRAIGHT! :eek: thanks and sorry for being so dumb!
In SQL in MS Access a '?' '*' '%' are all used as a wildcards.
I need to be able to retrieve all messsages which contain a '?' within the message. i.e. If a message contains the following characters : why?
I want to retrieve the message as a '?' is part of the message.
When I write the code :
SELECT User.User, Message.Message FROM Message INNER JOIN [User] ON Message.[Message ID]=User.[Message ID] WHERE Message Like '*?*';
Access takes the '?' as a wildcard and retrieves all the messages in the database. Is it possible to get round this and use '?' as a specific character.. not a wildcard?
On my form I have a number of buttons named A1 to A6. I want to be able to click on a button and bring up a report that thows all records A6 if that is the button I have pressed or A1 if that is the button I have pressed. I know I could make each query individually, each one looking for the appropriate data, but I was hoping I could make one query and have the criteria change automatically depending on the button that I press.
Is this possible or am I asking a little to much of Access!
I'm have a query with some fields but only one record. The values in the record depends on comboboxes in a form.
What the query is doing, is that is calculating percentage. So the problem is if you go from zero to something bigger than zero, then this this gives you an infinite value of percentage. I solved that by if it is a zero, then it's changed to null. Then the percentage will be zero.
What I want, or wonder, is if the value is zero, is it possible to to have a textbox or something "lightened up" in a form? What I mean is that if you do your choices in the comboboxes and this gives you a "bad zero" in the query then a text pops up and tells you that "the values are a little bit wrong" or something like that=)
More "shortly" said, can a texts visibility in a form depend on the value in a query?
I have always been wondering why this happens, and now I have a problem with it.
When I export a Table/Query from Access to Excel (be it with Right CLick -> Export...or TransferSpreadsheet) there is an apostrophe character (') appended to the front of some/each cell in Excel. You cannot see it immediately, but when you click on the cell, there is this character. WHY? Does anyone else have experience in this? :confused:
I would like a query to run, depending on a date..If the date in [Txt_Date_Account_Opened] is between todays date, and within the last 30 days.do CurrentDb.Execute ("Qry_Append_Units")else a msg box stating "account is not yet open"
I'm after a piece of code which works as described below.
I have a save button on a form
I have a append query ready to run.
When the save button is clicked, I want the code to see if a checkbox is true or not and if its true I want the append query to run if its not then I want the form to save and nothing else.
I have fought hard to try and figure this out and I have gotten 50% there.
I have a query that generates the following calculated fields through numerous calculations etc....:
[De-Merit Marks] [No Del with Major Faults]
Now the way it should work is that a supplier gets a rating (A-D) depending on both their De-Merit Marks score and the amount of deliveries with major faults. I used the following function in the query and it does the de-merit mark grading.
However I have no idea how to integrate the Major Faults part.
Basically:
0-10 = A 11-30 = B 31-50 = C 51-100+=D
If the supplier has one major fault, the rating should drop one from that reported using just the de-merit score. Two means it drops two ratings (so if an "A" on just the demerit, they would drop to a "C" and so on. Obviously a supplier cannot drop below a "D".
Can anyone suggest how these maybe acheived? I fear it requring VB code, but I am not that good with VB hence why I have relied on queries for this.
I am giving two tables and I need to create a macro that automatically updates these tables depending on the value of a Yes/No field. If it's No, it's in the 1st table TableOne, if it's Yes it automatically updates to TableTwo.
So, the best way I saw to go about is to set up an append query and then create a macro that runs it
So my tables have the values FirstName, LastName and isValid (more but keeping it short)
So for my append query, I put TableTwo in the pop up I get. Then, where it asks for the field I put it
I do this for all (it was autocompleted except the Criteria field). I tried to keep Criteria with data only for isValid but that didn't work. I wrote it for all the field names, still didn't work. Whenever I click run it says it'll append 0 rows.
I have a customer who made a query with a field that looks like that:
NewField: iif(Field1=True and Field2>100 and isnull(Field3);"Blue";iif(Field1=True and Field2>100 and not isnull(Field3);"Black";iif(Field1=True and Field2=100 and isnull(Field3);"White";... And so on, for miles and miles.
Although it works, and fast enough, I don't like the way it is displayed and I am sure that it could be even faster without all these iif.
Any idea about the best way for me to handle this kind of query? Thanks!
I have a main table which contains data on Oilfields. This is set up with an auto number for ID and a name of each oil field. I have other tables which contain things such as Pipelines, Wells and Platforms, which are linked back the the main table.
The problem is that the database was given to me with the links being between the Oilfield_Name, and not using the Oilfield_ID (which is not present in the 'satellite' tables). How can i add the oilfield_ID column to the 'satellite' tables so i can link them properly?! (i can obvioulsy physically add an 'Oilfield_ID' column to the 'satellite field', but how do i populate it with all the correct IDs from the main table, to suit what is in the 'Name' column of the 'satellite' field- does that make sense?). I suppose I need to replace all of the Names with the IDs?
This has been giving me such a headache. The Oilfields table has 2500ish records, with as many as 1200 in the other tables linked to it.
Hi, Im building a DB that basically tracks the hours people have worked, its all going fine apart from one major bug which i cant get my head around, I am trying to work out a way so that if someone decides to change the hours they have worked it replaces the existing record and does not create a new record.
Each record has a unique user ID attached and each day is dated, do you think this is a VBA solution or a change in the relationships perhaps?
Sorry fi there is already a similar answer to this question on the forum, but i couldnt find one!:confused:
Hi, I have 2 tables with similar data. However I need to compare this particular field called CompanyName in both tables. Reason being human error/exposition data errors. For example, Chef Kitchen Holdings Limited, in one table it is Chef Kitchen Holdings Ltd while the other table is Chef Kitchen Holdings Pte Limited, they both are the same but Access recognise them as 2 distinct datas.
Ｔｈｅｒｅｆｏｒｅ Ｉ need help in comparing datas between these 2 tables for this particular field. So long as there is 75% similarities, one of the 2 tables will have the data replaced.
Is that possible using Access? if not, how about excel? please suggest.
OK, here is my situation. I have an access database which is updated automatically every night with new data. My companies solution to this is each morning, the entire database is downloaded to my computer to be used by another application.
In one of the tables, there is a field that doesn't work for my purposes, so each day I have to manually open the table, and change the contents of that field for every record. For example, lets say I have the following table:
I am having a query, where a few date records are null because of no entry or record, what a want that the field with no date entry to be come up with zero "0" .
