Hi!
I've written a querry - and the results that come back dont look right. Some rows have data in and others dont.
Has anyone seen something similar?
Does this mean that the data are probably incorrect?
Any advise?
A.
I have table1:
Name, Date, Points
Peter, 1.8.2005, 100
________2.8.2005, 200
Paul, 1.8.2005, 100
________4.8.2005, 300
etc. and I need to fill the blanks with Peter, Peter... , Paul, Paul, ... etc. so, that I can later take it as a group ID and work with the data.
Preferably with SELECT query, but insert or update is also possible.
hi all
i have created a database but didnt insert any default value at the begining and now i have blank cells in the table rather than £0.00
how would i go about querying the blank cells and then populating them with 0.00
thanks in advance
chris
Hello,
I have an Excel file that I use to enter data. About half the columns are validation cells to minimize human error. The validation cells are in rows 2-200. I then import the data into Access.
The problem is that, even if I only enter or select data in one row, Access imports all 200 rows that contain validation cells. I only want the rows in which I've actually entered/selected data to be imported.
I've searched this forum, but may not be using the correct search criteria.
I've also searched Google for "Access Imports Blank Validation Cells from Excel" and other variations of the same words, and switched empty for "blank".
Can someone recommend a thread or on-line article that will give me an idea how to work around this?
Thanks in advance for any assistance.
PS. Sorry, I wasn't sure under which category to post this.
Again, thanks for your time.
I think I read somewhere that DCount will not count blank cells, but when I try to do it - it counts everything.
Here is my formula:
=DCount("[E-Mail]","Detail","Not IsNull([E-Mail])" & " AND [Primary] = [Products]![Product Code]")
Basically, I want to count the number of email addresses in the E-Mail column of my Detail table IF the Primary column (also in the Detail table) equals the product code on my form AND if there is something in the E-Mail column for that line. When I did the above formula - and I've done a bunch of different variations - it keeps counting all lines that match the product code.
I use this on most queries where I need to return all results if the form field is left blank. Works like a charm every time...
Like [Forms]![frm_main_menu]![Week] & "*" Or Is Null
except for this time..I need to filter by week number (52 weeks in a year)...problem is if I enter week "1" I also get weeks "10, 11, 12, 13, 14, 15, 16, 17, 18 and 19".
Hi All
I'm currently working on a product catalogue, i have a product data screen (frmProductLookup) one of the field s i have is "Replacement" for when a product is superseeded. What i want to achieve is to be able to click on a button and open a second product data screen that automatically goes to the record for the replacement product.
I've currently tried creating a duplicate data form (frmProductLookup2) and used the openform wizard, but for some reason this pulls up a blank record...any ideas
cheers.
I have a switchboard which runs a google style search query to find people based on their first name, surname, date of birth or NI number on clicking the search button. It uses the following criteria in the query Like "*" & [Forms]![SearchF]![Firstname] & "*" for each of the above fields.
This works perfectly until someone inputs a record that doesn't have anything in one of the fields. ie, full name and DoB, but no NI number. When a record is entered in this way, the table stores the record but the search query cannot find it.
Hi
I have set up a form to run a query with the criteria linked to the options/drop down menus on the form. I am able to search for criteria set out in the combo boxes. However, what i would like to ensure is that if the drop down boxes are left blank the results will be to show 'all' results for that field.
help is greatly appreciated!
thanks in Advance!
Shapman
Hi All,
Yes ok DDE may well be dead but it works for me so why change it? Basically I wondered what the commands where to format Excell cells. For example if I stamp a cell with todays date:
DDEPoke intChan1, "R3C2", Left(Now(), 10)
How would I say shade that cell black, change the lettering to white or underline or bold that cell?
Any help most appreciated.
Regards,
Dalien51
OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.
I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).
I am using a form to query the table...no problem. The form has text boxes the user filter down the data
The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!
Things I have tried:
1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing!
2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)
what else can I do?
Thanks
Is it posible to import only some CELLs from excel file to access. for example i wont to import only A1 , B10 , E14 , C3 etc.?
View 1 Replies View RelatedHi everyone...
I'm a high school student working on an Access project for a summer internship. I needed your assistance in writing a criteria for a select query.
Table1 has the following fields:
ID, First_Name, Last_Name, Org, Email, Status
Only "Email" is mandatory, ID is autonumber, the rest are optional.
I have to create a query that will allow users to search the table with any of the fields above. A user may search with only one field, e.g. all users where "org" = "YMCA"
Presently, I am using the similar criteria for all the fields:
Like "*" & [Forms]![Search]![txt_FirstName] & "*"
The problem occurs when, for example a record exists with the following -
First_Name = Null or Blank
Last_Name = "Smith"
If you search for "Smith" in Last_Name, then the record does not show up, because First_Name in the record is blank.
How can I alter the criteria for it do search correctly?
I already tried:
Like "*" & [Forms]![Search]![txt_FirstName] & "*" & ""
Thanks,
Gautam
Hi everyone...
I'm a high school student working on an Access project for a summer internship. I needed your assistance in writing a criteria for a select query.
Table1 has the following fields:
ID, First_Name, Last_Name, Org, Email, Status
Only "Email" is mandatory, ID is autonumber, the rest are optional.
I have to create a query that will allow users to search the table with any of the fields above. A user may search with only one field, e.g. all users where "org" = "YMCA"
Presently, I am using the similar criteria for all the fields:
Like "*" & [Forms]![Search]![txt_FirstName] & "*"
The problem occurs when, for example a record exists with the following -
First_Name = Null or Blank
Last_Name = "Smith"
If you search for "Smith" in Last_Name, then the record does not show up, because First_Name in the record is blank.
How can I alter the criteria for it do search correctly?
I already tried:
Like "*" & [Forms]![Search]![txt_FirstName] & "*" & ""
Thanks,
Gautam
Hi,
I have been tasked with completing an audit of approximately 10,000 items, to which I have generated a list of 40 questions (fields) for each record. I began collating the information in Excel, but found myself getting lost and even experienced major data loss on at least 2 occasions. I have now decided to ditch Excel and use Access 2003 instead.
I have imported my master spreadsheet, however as I have various contacts sending in their respective information in Excel spreadsheets with same types of fields, and also need to import data that has already been sent in. I’m thinking that it would be better for me to create update and append queries, especially as there is going to be a stage 2, where I will be requesting additional (field) information.
There is a big chance that some of the contacts will send in info for the same item (record), which means that there is a risk of duplication, which I have removed as I have a unique identifier which will be the primary key! Is that right or should it be indexed?
The main problem that I have is that I want Access to ignore the target cell if it has a value in it! Meaning that I would not like Access 2003 to overwrite the cell with valid data in it, with a blank cell! As I need the database to grow!
Can anyone suggest a way that I can do this please.
TIA
Bazdaa
My understanding of the LIKE operator seems to be deficient for filtering a query when using the Access query pane. I have a combo box that lists the values to be filtered. One option in the combo box is "No Selection". If "No Selection" is selected, I want the filter turned off, in other words ALL records are shown.
In theory the following code would seem to be correct when placed in the criteria line. It does not work: IIf([forms]![type5frm].[combo2]=24,Like "*",[forms]![type5frm].[combo2])
If the value of COMBO2 =24 (No Selection), then I would like all records to show up, but no records are displayed. The FALSE side of the IIF statement works correctly.
As an experiment I tried LIKE by itself and it displayed all the records: LIKE "*"
Another failed try:IIf([forms]![type5frm].[combo2]=24,[agency]) Like "*",[forms]![type5frm].[combo2]) AGENCY is the fieldname.
A more exotic attempt that failed:IIf([forms]![type5frm].[combo2]=24,Val(Str([agency]) Like "*"),[forms]![type5frm].[combo2])
Any thoughts?
Hi ,
I am using Access Application with Orcale Db as backend and this application is already in production.
The problem is.. USers Complained that the system is slow, this is because there is refresh (requery) for each Insert (When they click submit, that particular record is inserted and then a refresh has to be done to bring only unused member IDs on the User screen), i had used a Sql query using NOT IN , then i realized and now changed it to NOT EXISTS, but with this new query , if i test recordset. EOF , even though the query returns more than 13000 ..records, EOF sets to TRUE and the other part (not supposed to) gets executed. I really cant figure out why, i took the same query put it in query builder in Access and ran it , it gave 13000 records..but in VBA , this recordset is not giving expected results and so..i am totally confused..The part of the Code is shown below.
Code:newquery = " SELECT A.MEMBER_ID, A.MEMBER_NAME, A.ADDRESS_LINE1.................. FROM TABLE A WHERE NOT Exists ( SELECT '' FROM TABLE B where A.MEMBER_ID =B.MEMBER_ID)AND A.MATCH_LEVEL <> 0 ORDER BY A.MATCH_LEVEL DESC"rst1.Open newquery, Cnt, adOpenDynamic, adLockOptimisticIf rst1.EOF = False ThenForm_PHS_ASSIGN.RequeryForm_PHS_ASSIGN_SUB.RequeryElse MsgBox " No records to Process!" End If
EVEn though it should requery , it gives a message No records to process which is wrong. It works with NOT IN.... BUT VERY SLOW, NOT EXISTS IS FAST BUT DOES NOT GIVE ME WHAT I WANT..
Where am i going wrong..help pls!!!!
THANKS VERY MUCH!!
I have created a switchboard and some additional forms. I would like the frontend of the database to display my forms in a maximized view when the users opens them. Can anyone advise me how to do this?
For some odd reason when I make changes to the forms (example) resizing them my clicking and dragging the edges, the changes will not save. Can anyone advise me what I may be doing incorrectly? :confused:
I am using Access 2000 and have the following issue I need assistance with please.
I initially had no primary key set on the table and the form and all the data from the tables showed fine in my form. However, I wanted a primary key to prevent a user from accidentally typing a duplicate Repair Order number in the form.
I went back into the table and set a primary key as our company’s Repair Order number. Since this number should never be duplicated I wanted this as the primary key. I had a ton of issues and kept receiving messages that indicated I had duplicates in the table. After running a query through the wizard to find the duplicates in the Repair Order column, which the query could locate, any, I finally exported the data in the table into a spreadsheet. Then I created a new table and set the primary key to Repair Order Number and set the index in the properties to “yes, no duplicates”.
However now when I go over to my form, the field for the Repair Order number, Model number, Serial number and Phone number now show this error message in the field #Name?, and will not allow a user to type any text or numbers into these four fields. The other fields do show the data from the table. I went through help for information on #Name? and verified that I do have Msowcf.dll and it is not missing from my computer.
All the other data was pulled from the table into the form except for these four fields. I did verify that there are no duplicates in the Repair Order column of the table but the Model and Serial numbers can have duplicates as well as the phone number.
If I change the index properties under the primary key of Repair Order Number and tell it to allow duplicates these issues go away but it seems I can't prevent duplicates from occuring in that particular field then.
Another note. I do have fields that work from a “Date of Service” that calculate out adding 10 months to the Date of Service as a contact date and another field that calculates out 12 months as an annual service date. Those seem not to be affected and the formulas are working fine in those fields on the forms.
I am not very familiar with Access and I am still in the learning stages. Can someone give me some insight on what I may have missed or need to correct? Please be specific. Any assistance would be greatly appreciated. This one has had me stumped for several days.
I receive a bunch of excel files with items to be added to a database. some of the items are to be added as new, some to be deleted and others to be modified.
I identify the action (add/delete/modify) by looking at a column in the excel file e.g. column A.
At this point I am fine with the add/delete because I can filter the data or bring all the files to a temporary table in an Access database via vba script and then running queries to do the rest.
My issue is that the spreadsheets have a lot of columns and for the modifications what they do is use the same file for instance, make changes on the cells (any cell) that need modification and then highlight, bold or underline only the items that need modification and ignore the rest of the file (a tedious process since I have to then open each file and manually modify the database).
I'm trying to export my queries into an Excel product color coded to one simple field. for example if a aircraft in maintenance will only be available for four months from oct-Jan in the Pacific i want it colored blue. I want it keying off the region EX: (Pacific) So far It shoots out the product with one color, and I have to manually change the colors in Excel.
View 2 Replies View RelatedGood morning,
I am having some kind of problem. A query in Acces that is used to find results in the DB is asking 2 time's te input.
And there is no way that the input boxes is asking to do this twice..
here is the Query,
SELECT tblHotlines.Hotlinenummer, tblHotlines.Vestigingsnummer, tblDealers.Postcode, tblHotlines.Datum_hotline, tblBestellingen.Chassisnummer, tblBestellingen.Onderdeelnummer, tblBestellingen.[Uitleverings datum], tblBestellingen.[Vervangende auto ingezet?]
FROM (tblHotlines LEFT JOIN tblBestellingen ON tblHotlines.Hotlinenummer = tblBestellingen.Hotlinenummer) LEFT JOIN tblDealers ON tblHotlines.Vestigingsnummer = tblDealers.Vestigingsnummer
WHERE (((tblHotlines.Vestigingsnummer) Like [Geef het vestigingsnummer op] & "*") AND ((tblDealers.Postcode) Like [geef evt een gedeelte van de postcode:] & "*") AND ((tblHotlines.Datum_hotline) Like [Geef de datum op:] & "*") AND ((tblBestellingen.Chassisnummer) Like [Geef het chassisnr op:] & "*") AND ((tblBestellingen.Onderdeelnummer) Like [geef evt het onderdeelnr op] & "*"));
Qhat could be the problem that Access is running a Query twice before getting results
I'm new to Access, but a coworker has a huge project to undertake. There are over 200 queries that produce 1 row results for each query. She's been cutting that row and pasting it into Excel 200 times. Is there an easy way to insert them 1-by-1 into Excel or prefereably Access?
BTW, the columns are the same on the query results. I'll keep investigating while I wait for the experts to answer.
I have a crosstab query which shows me something like:
Activity Quarter1 Quarter2 Total
Call 2 1 3
Visit 3 1 4
Home 4 2 6
and I want to add another row in the query itself or a new query to SUM the Visit and Home.. Like for example:
Visit+Home 7 3 10
Is it possible to do that?
I am writing the following code that will first of all display column headers dynamically using "Headers" field data from Access table and then find out the sum(volume) using column header and first column values. The following code works fine to display headers dynamically in Excelsheet from Access table but doesn't display sum(volume) in all the corresponding cells. As I can't attach the Access table so I have stored data from Access table to sheet named "Access Data" as attached. The sheet2 named "Report" should populate total volume .
Code:
Public Function Inputdata()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim i As Integer
[code]...
For instance, first table export to EXCEL CELL A1 and then second table export to the same EXCEL but to CELL A5! I simply do not know the sytax to tell ACCESS to do the correct export!
e.g. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "EXPORTDATA", "c:EXCELSHEET.xls", True