Search results

Can't insert records - Validation Rules Hi, I'm trying to insert data and am getting the following error: 'Microsoft Office Access can't append all the records in the append query. blah, blah ... and 1 record(s) due to validation rule violations' The problem is due to a field (that I'm not...

ha! talk about trying to make it too difficult i used not in, instead of not exists. too easy

Hi, I'm stuck with a tricky (to me :-) bit of sql that might want a 'not exists' clause somewhere, i think. I'm trying to count local council election data; the tables are like this: A Council (tblCouncil) has (1 or more) Wards (tblWard). Each Ward has a field: VacancyCount Wards have (0 or...

sorry for late reply, afk over the weekend. No, from now on, input will either be into TypeA or TypeB, depending on the inputter. There are some rare cases where an inputter will add to both types, and that is the reason for the change.

oic, like excel's PivotTables. I don't think this is the solution for my problem. I'm not going to be analysing the data I'm working with, I'm trying to refactor a table. Once the change is made, I won't be interested in the two types again except for entering data, and having the two columns...

namliam said: Why the update? We've found that the Count field is misleading, and needs to be separated into two Count 'types'. My plan is to run this query to get the TypeA's into TypeACount, and then run another update query that gets all the TypeB's into TypeBCount, and then rip the...

Hi, I'm trying to do an update query but I'm getting something wrong Table1(ID, Name, TypeID, Count, TypeACount, TypeBCount) Table2(TypeID, TypeName) update Table1 set TypeACount = Count where TypeID in (select TypeID from Table2 where TypeName like "*Type A*") I want to put the value...

thank you Galaxiom, Khawar, got it sorted. I think i was making it too difficult. cheers

Ok to the response buttons having their own click procedure. Ok to these procedures closing the popup form, but the 'new' form will still be open in the background. On hitting yes, I want to close the (popup) form, and follow the 'True' path of the if branch I can't set myForm = true in the...

Hi all I'm looking for a thread on how to use a form as a MsgBox. I've seen this posted as a solution to customising a prompt as in 'how do i set different colours/fonts/buttons etc in a MsgBox?' and I want to give it a shot. My problem is how to call a form to return a value. Ie, i'm...

umm, your query would probably look something like select maturity, trade_date, ((maturity - trade_date) * interest) as the_answer from my_table where trade_date < date() where interest is a number assuming the answer is calculated by subtracting the trade date from maturity to get the number...

This sounds more like spreadsheet work than a database. Is the answer calculated? ie can you do a calculation on trade date and maturity to get the number you want or is it simply looking up a value that has no connection to the fields? If so you might be able to solve this by performing that...

Hi all I have a query that uses another query in its definition. I've edited that subquery to make it run faster, and both versions return the same data. When I investigated the new subquery, I found the only difference was the order and join type of the tables in the FROM clause (a couple of...

I gemma, LPurvis I think I've sussed it. I've 'Dim'ed the QueryDef and Recordset at the top of the module, for reuse in the different procedures. Everything works, but in this instance, this query runs while there is an existing query running (I'm pulling the surname of a candidate - usually I...

I have a (DAO) QueryDef and RecordSet set up with standard code. Set qdf = CurrentDb.CreateQueryDef("", sqlGetSurname) qdf.Parameters(0) = candidateID Set rst = qdf.OpenRecordset 'check we have only one row If Not (rst.BOF And rst.EOF) Then Everything works fine up to here. The...

What are you using to display the data? If it has a 'Column Widths' property, you can use .ColumnWidths = "semicolon;separated;integers;in;twips" to set your columns. (There are 567 twips in a cm). Set the widths of the columns you don't want to 0. Eg, lstBox.ColumnWidths = "0;1134;567;"...

Hi all, I get the following error when trying to set the picture property of a toggle button: Microsoft Office Access doesn't support the format of the file 'C:\Temp\btnOn.GIF,' or the file is too large. Try converting the file to BMP or GIF format. It's a 2kb GIF created in ms-paint. When...

hi, just checking whether this is expected weirdness: I am running a union query involving a table designed with lookups*. Two looked-up fields in that table are requested in the query and when I run either branch of the union individually, the looked up info in both fields is returned...

a-ha I didn't set the RowSourceType property. When you set the RowSourceType property AND supply some sql to the RowSource property, you get values in your list box. woo-hoo! PS: I've set the params according to DCrake's 'use a get() method' idea, and I'm using a temp querydef so there's...

Hi Galaxiom you're not the only one running out of ideas. :) At the moment, I'm taking DCrake's advice and trying to separate all the code bits from the form and database bits. I'm doing this more because I 'get' the correctness of it, rather than because I think it will help as, in this...