I have a series of append queries that run when a database opens, to copy over the contents of various log files into a single table.
Each record has a field, [1-DateTime], which (surprisingly!) contains a date/time. When I open the linked text files from Access, these appear absolutely fine, all in US format (mm/dd/yyyy hh:nn:ss). But when I run the append query, it gets all confused, and puts them into my table in a conbination of formats!
Those that it can recognise as UK dates (e.g. 01/05/06) it stores as 1st May 2006, but then it gets to 01/22/06 and it decides that they must be US dates, so stores them (CORRECTLY) as 22nd January 2006.
Hi all. Having searched around I cannot seem to find what i'm after. I have a database which i hope to give users the option to run an append query which will copy certain pieces of information form one weeks to a another week.
Originally I thought about w/c + 7 which would give me a new week, but not all users were copying from one week to the next sometimes they would want to copy from one week to a date that was 2 or 3 weeks later.
What I can't find out is how to make the query append this date field to a date that is specified by the user.
Any ideas please so that I don't have to make some people angry with me.:(
I am trying to run the query below to tell me how much stock a company has available by deducting the amount dispatched from its allocation. I am getting an error message;
Run Time error '3061' Too few parameters expect 2
The code is;
Dim db As DAO.Database, qr1 As DAO.QueryDefs Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset Dim varVal0 As Variant, varVal1 As Variant, varVal2 As Variant Dim strSQL As String Set db = DBEngine(0)(0)
VarVal0 = Me.WINENUMBER
strSQL = "SELECT Sum(tbl_Data_DispatchLineitems.Amount) AS Amount " strSQL = strSQL & "FROM tbl_data_DispatchDetails INNER JOIN tbl_Data_DispatchLineitems ON tbl_data_DispatchDetails.DispatchID = tbl_Data_DispatchLineitems.DispatchID " strSQL = strSQL & "GROUP BY tbl_data_DispatchDetails.TradingName, tbl_Data_DispatchLineitems.WineNumber " strSQL = strSQL & "HAVING ((tbl_data_DispatchDetails.TradingName)=[Forms]![frm_data_Orders]![TradingName]) AND (tbl_Data_DispatchLineitems.WineNumber = VarVal0)"
Set rs2 = db.OpenRecordset(strSQL) rs2.Edit varVal1 = rs2![Amount] rs2.Close
I assume the error lies in the final line of the query code. Any help to find the error would be appreciated.
The Name_ID in each Donor Table points back to the Name_ID in Table1.
What I want to do is get a list of the Name, Address, Item and Item_Description for any record found on either Table2, Table3 and Table4 and then sort the result set by Name.
My tables are all very small – 100 entries at most on any 1 table.
My queries so far are returning 40,000 and 50,000 records – I assume because I am writing SQL that forces sub-queries.
I am more familiar with SQL using Where Tbl2_Name_ID equals Tbl1_Name_ID or Tbl3_Name_ID equals Tbl1_Name_ID or etc rather than inner joins or left joins so I am struggling.
I know this has to be elementary, but I am missing it.
I'm trying to show an overview of the green and red cars which were bought in june or before, ordered on price, I have this:
SELECT car.carname, car.colour, car.Bdate, car.price FROM car WHERE (((car.colour)='red') AND ((car.Bdate)<6)) OR (((car.colour)='purple')) ORDER BY car.price;
It works all except for the <6 part (doesn't work with =<6 etc either). The month is only used in this column and represented by a number i.e. 6.
I hope this is clear and I hope somebody can help me with my problem!
I have a listbox that populates it's values from a table. Upon the click() event, I run a script to determine the current record for the rest of the form. One of which is an TextBox bound a field in the table the ListBox is populated from.
I store the old values in the Form_Current() method, and allow the user to change the values in the TextBox, and upon textbox_change() I enable an 'Apply' command button.
When the Apply Button is pushed, I set the "saved" data to the actual current data (since the control is bound to a record field), and thus the changes are fully applied. However, I cannot seem to get the ListBox to update it's 'text' display to represent the changed value from the textbox. I have tried ListBox.requery, but it doesn't work instantly...it sometimes is delayed until I change "apply" a new set of changes.
Why is this?
Specifics: Table AreaID (AutoNumber) Area (Text)
Query Table.*
AreaList.RowSource = Table AreaEdit.ControlSource = Area
Private Sub AreaEdit_Change() Dim St As String St = AreaEdit.Text Debug.Print "Chg Text: " & St Call UpdateChanges(True) End Sub
Private Sub UpdateChanges(ByVal Value As Boolean) ChangesMade = Value ApplyBtn.Enabled = ChangesMade End Sub
Private Sub ApplyBtn_Click() AreaList.SetFocus 'this is because you can't disable a control (the applybtn) when it has the focus AreaList.Requery '<==== THis is supposed to repopulate the listbox, but it doesn't do it. Call Form_Current 'this just stores the current values of the actual record into temp variables Call UpdateChanges(False) End Sub
hi ..had a quick search through the pages, no luck.
i have 8 check boxes on my Search form. If i check one of them...i want to send certain text to a field. is this possible? eg: i have 8 check boxes with different project names, if i check my check box named "Laem Supot" i want the the text "Laem Supot" to be sent to my Project Field on my listbox.
i found a similar for a date value:
Me.yourcontrolname.Value = date() .....(would this work and how do i implement it)
however i may need to check 2 or more check boxes, therefore i can't send 2 project names to one field. My clients may be associated with 2 or more projects, how do i show this or represent this in my SEARCH FORM.
Yesterday I posted a question and rec'd good advice but as usual I don't think I explained myself very well. As the attached picture of my Form shows, I have a checkbox for each Age Group under each Team. I originally had a query that would come up for each Team's particular Age Group and then the required data copuld be input. This leads to too many querys clogging things up. I tried to copy the SQL code of one of the query's to the On_Click event of the checkbox but as I have been informed SQL is different to VB. All I would like to know is how to convert the SQL to VB coding so that I can continue to do the coding for each Checkbox. The SQL code for the first checkbox is:
SELECT tblPlayerRegister.Surname, tblPlayerRegister.[First Name], tblPlayerRegister.Age, tblPlayerRegister.[D'n], tblPlayerRegister.G1, tblPlayerRegister.SP, tblPlayerRegister.Age2, tblPlayerRegister.G1A FROM tblPlayerRegister WHERE (((tblPlayerRegister.Age)<11) AND ((tblPlayerRegister.Club)="Beaconsfield")) ORDER BY tblPlayerRegister.Surname, tblPlayerRegister.[First Name];
If there is an easier way, by all means let me know. Any assistance would be greatfully appreciated.
Im trying to figure out how to model 'Printer' and 'Cartridge' information. The relationship between them is Many to Many, but to add to my confusion, more than one cartridge can be used in a printer (Black + different colour cartridges). Adding a new entity to resolve the Many to Many does not solve this issue of how a printer can have, for example, a black cartridge and 3 different coloured cartridges.
Any ideas how to model this please?? I have been toiling with it for some time and I keep going round in circles :confused:
I'm sure it shouldn't be as difficult as it seems, so I'm hopeful someone might be able to set me straight :o
I have been unsure about this topic and want to finally be sure.
When I create a lookup table I don't include a PK; I just have the one field. Therefore I don't include an FK within the Main table; I include the same field name as the field name in the lookup. "sector Details" in lookup and "sector Details" in Main.
Am I better to make up these lookup tables and use a PK and then have the FK within the Main Table? Is this easier or trickier to work with? And will it cause any problems if I did include keys? for example, providing combo boxes as search criteira for forms using the FK/PK as bound fields?
I know it's a bad idea to have lookup fields in tables which I'll avoid. But this means if I'm updating in the back end I'll only see a table full of keys and not the field name!
Hope someone can help because I've been getting different advice since I started on Access including Professional books using lookup fields.
I have been doing some experimentation with Queries and I have discovered something unusual.
If I have one query and I use the totals feature (only using group by) I get all the records based on the inputed criteria
If I then use that same query as an underlying recordset for a second query and I use the totals feature (only using group by) in the second query... I only get the first record of each type. See query results below..
I am trying to achieve the results of the second query in the first query (DON'T WANT NESTED Queries) and I don't really understand why the results of the queries are different when everything else is the same other than one being nested.
Example below
First Query Results (using totals with group by)
1 a 1 a 1 a 2 a 2 a 2 a 3 a 3 a 3 a 4 a 4 a 5 a
Second Query Results with the First query as its recordset (using Group By)
I do have questions about tabular subforms. I have created several forms and I thought that they were linked together. The main form (GSI) is general information about each site that we visit. The tabbed forms are about various survey's done on a site. So, what I thought would happen is; I'd fill out the info on the GSI forms about each site. I'd go back after doing a survey, look up that site, click on the corresponding survey tab and add information about that survey and It would link back to the site on the GSI. What is happening is, when I pull up a site and add survey data the same survey data comes up in the next site also. I think, the problem is,
I have a switchboard with Add new site and Edit site. When I go into Edit site and I do a search by Site name, it pulls up the name, but not the record, so I'm just changing the site name by doing it that way. If I move down to the bottom of the form and scroll or move through the records that way it works right, but I need it to be more user friendly. I'm sure that there is a statement I could create to do this, but I'm not knowledgeable enough to know what it is. :confused:
I'm running Access 2000 through Citrix, 20 users internationally from 1 db. I'm working through Citrix network issues to split db with separate FE's for each user. I have sporadic corruption due to this. But this morning I met a new breed of corruption. I preface this with the fact that I run a bat file nightly to do clean up and compact and repair the db. It verifies the db is not in use prior to running. This morning we were presented with a hosed db. The log file said updates ran fine. In my main table the first record was garbage, including the Auto-num field. I found that my Primary key has lost it's index and references had become unchecked. Correcting these issues got it running but does anyone have any clue what might have triggered this or even where I should start looking? Please help as I am at a loss. Thanks.
In OPTIONS -> Advanced I have the option Open Database using Record-Level locking ticked.
What is strange is record level locking works on Form1 (Form1.recordlocks= Edited) as long as Form2 has Form2.recordlocks = No Locks. If I change the Form2.recordlocks = Edited, Form1's locking becomes page level locking.
This is even true if Form2 is not open...
Additionally, Form2's locking is always page level locking if Form2.recordlock = edited.
I would like all my forms to perform RECORD level locking not page Level locking. How do I accomplish this.
I've been trying to resolve this issue for about 2 months now but no answers from the community.
hello, this is frazzling my brain i cannot comprhend the answer :confused: sorry werent sure were 2 post this hope this is the right place
I have an array of comics 300 they come out weekly, the first is Number: 344 this came out 26th September 1983, i have a form with a combo box were i select the comic number (344 - 700+) in this case i am selecting 344 and i press a button "command 11", i would like it to display in three text fields, date, month and year. This has well an truly killed off my brain. Any help would dearly be appreciated. Need any further information just ask. Paul
I have created a database in Access XP (2002). In a nutshell, the database records numbers of people attending a seminar; which can take place any number of times per week, and so hence can take place any number of times per month.
I have set up the query so that it can run immediately after a seminar to show the attendants who attended the seminar on that current date "Date()" in the criteria box. However, how would I go about setting it up so that it shows who attended every seminar in the current week or month?
hi Guys, I have been looking at different post and checking Microsoft help files as well, but still can't seem to fix this problem.
I am having 2 tables. The first table is connected to a form for viewing and entering data, and in the second table i am just copying 3-4 fields from the first table.
I am trying to use the insert statement to insert records in the second table, and everytime i click on the "Add" button to add the records i get the following error "MS access can't append all the records in the append query ... blah blah blah"
However if i close the form and reopen it, and goto the record (as it is saved in the first database) and now click on the add button to add the fields to the second table/database, it works.
I´m trying to improve my appl. but I´m stuck trying to figure out how to append multiple rows on a table using the Append query.
My problem comes since I have 4 tables with the following fields: 1) Orders OrderID (number) CustomerID (number) Date (date) etc... 2) OrderDetails OrderDetailID (number) OrderID (number) ProductID (number) QuantitySold UnitPrice etc... 3) TmpOrders OrderTmpID (Autonumber) CustomerID (number) Date etc... 4) TmpOrderDetails OrderDetailTmpID (Autonumber) OrderID (number) ProductID QuantitySold etc...
The problem comes since the Tmp tables are used just to record temporarly the information before the transaction is completed. Whe the salesman at the desk finish the sale, a command button is presseed and an append query runs to transfer the data from Tmp tables to the Definitive tables. Another query (Delete) is excuuted inmediately after the append qry. and it deletes the information recorded on Tmp tables.
I need to reset the autonumber or create a field that records the line number so I can add up to the Maximum OrderDetailID found on the definitive table, but the problem is how to create the "controlled" autonumber.
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example: table1 01/01/2014 15/01/2014 17/01/2014 30/01/2014
Explanation: Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates) Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2 Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100 Matt 15/5/11 15/9/11 7/7/11 £200 Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
