Search results

So short answer : if writing SQL in VBA use mm/dd/yyyy by applying the format statement?

Thanks. So I have a datasheet based on a query. The query looks like: strSQL = SELECT MAS.ID AreaNumber, tifDate, assignmentDate, status FROM MAS WHERE assignmentDate = (SELECT MAX(assignmentDate) AND status = 'Complete'; So the date fields references in the SELECT part of the statement are...

Hello, I understand access tries to be smart with dates. Here in the south pacific we use dd/mm/yyyy. The net result is access is not smart and across my forms I seem to have a bunch of dates that end up in US format and if it gets a date where it "wont compute" for mm/dd/yyyy then it will put...

Hi All, I have a datasheet view and wanted to display a little more information about the current record the mouse is over. I was going to try doing this with controltiptext/hover but I think after some research maybe this only applies to buttons etc? Can anyone clarify if this is possible...

Good to know. I finally got everyone out for a C&R - which seems to have fixed things so far. Fingers crossed.

Can you rebuild just one table? How?

Is this process easy? There are quite a few tables, reports and queries. Some of the tables are linked one to many so if I delete the problem record I would end up with orphans on other tables.

Hi Yes it is in VBA in which case the whole statement is in " " with the string inside '' but for debugging purposes I was running the query in the query window in which case the string was just in ''

Hi, I have a query which works fine for some records and not others. It is a very simple query: SELECT * FROM MAS WHERE systemNo = 'B123456' The records that don't work the error is: Runtime Error 3049 Cannot Open Database ". It may not be a database that your application recognises To...

Bingo! Thanks.

Hi Guys, I am stuck on this one. If I try the SQL: SELECT * FROM MAS I get the expeced 1500 odd records. If I try: Dim rs As DAO.RecordSet Dim db As DAO.Database Dim strSQL As String strSQL = "SELECT * FROM MAS" Set db = CurrentDb Set rs = db.OpenRecordset(strSQL)...

Thanks to you both. Snuberg you were right on the money.

HI, I have a query recordset that I can not update - the little message in the status bar says something to the effect of this when I try to update in the form. My database is split frontend/backend and I can update the data tables on there own from the frontend. I have also tried this from...

Thanks - the working solution: SELECT Format(DateValue(DateStart),"dddd") AS DayOFWeek, Count(Table1.DateStart) AS DayCount, Sum(IIf([PATIssuedYN]="Yes",1,)) FROM Table1 WHERE (((Table1.DateStart) Between DateAdd("d",-7,Now()) And Now())) GROUP BY DateValue(DateStart);

I have data in MS Access like: +------------+-----------+ | DateStart | PATIssued | +------------+-----------+ | 12/12/2004 | Yes | +------------+-----------+ | 13/12/2004 | No | +------------+-----------+ | 14/12/2004 | No | +------------+-----------+ |...

This works: Private Sub btn_transfer_Click() Dim db As DAO.Database Dim rsTarget As DAO.Recordset Dim rsSource As DAO.Recordset Set db = CurrentDb Set rsSource = db.OpenRecordset("SELECT * From SOURCE WHERE SalesNo='" & SalesNo & "';") Set rsTarget = db.OpenRecordset("SELECT * FROM...

Ok, New approach using DAO. I am stuck on how to get the value for the corresponding field from the form while looping over the recordset? Private Sub btn_transfer_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb Set rs = db.OpenRecordset("SELECT * FROM SAM_NTH...

Hi, I really should have called this moving a record since it is being moved not copied - my bad. In terms of the data structure yes maybe some bad calls were made there, but Table A and B both now have quite a bit of data in them which uses the auto number. In terms of what I have suggested...

Ok, I have managed to successfully copy the ADODB recordset object into rsTarget which I now need to figure out how to insert into my target table? Code below so far for anyone who can help please? Private Sub btn_transfer_Click() Dim conn As New ADODB.Connection Dim Field As ADODB.Field Dim...

Thanks docman. I still cant quite get it right. adFldIsNullable should take care of empty fields in the recordset. I can simplify the code to add the field and the value in one go: For Each Field In rsSource.Fields Debug.Print (Field.Type) If Field.Name <> "ID" Then...