Search results

Hi all, I am getting error: Data type mismatch in criteria expression. (Error 3464) the code that creates it is: updateuser2 = "UPDATE entitlement SET Entitlementfld = '" & holidaytotal & "' WHERE id = '" & usersidnumber & "' AND yearfld = '" & nine & "'" holidaytotal, usersidnumber...

Hi all, Basically I an updating a table (called tbl_users) using this code: updateuser = "UPDATE tbl_users SET hourspw = '" & newhours & "' WHERE (tbl_users.name = '" & personsname & "')" 'AND [Start_Date] > DateAdd("m", -12, Date()) AND [absentcode]='countme')" CurrentDb.Execute...

Hi John, I haven't finished with this yet and this is just a quick post to show you how im getting on. I think it is working how I want it to now, below are the relavant codes - what do you think? Like I said its not finished and there are probably a few errors but i'll check through it again...

Hi John, I have changed all the above and it is still erroring saying it has too few parameters - expected 4. When you run the query on its own it asks for holidayyear, entitlement year, usersid and usersid1 and surely in the vba code it should place these values in automatically? Thanks...

John, This is my code so far with a couple more parameters in code: Dim dteDateSelected As Date Dim booCarryOverDate As Boolean Dim qdf(1) As QueryDef 'Dim qdf(0) As QueryDef Dim rs As Recordset Dim intDaysRemaining As Integer Dim userstotalid As Integer dteDateSelected =...

I tried that and commented them out of the vba code, now getting the expected 4 error on the original error line. Any ideas? cheers for all this.

Hi John, I get the run error 3129 Invalid SQL statement on those lines - i think that is why I commented them out. At the top of the queries I have: PARAMETERS EntitlementYear Long; Should I have this in both the queries and vba code? if so any idea on the error? Thanks Martin

Hi John, Thaks for the reply - looks great, Just one thing - I am getting an error here: Private Sub txtEndDate_AfterUpdate() Dim dteDateSelected As Date Dim booCarryOverDate As Boolean Dim qdf(1) As QueryDef Dim rs As Recordset Dim intDaysRemaining As Integer dteDateSelected =...

Hi john, cheers for the help so far - I'll try and explain. a20091 returns to values when run, the ID of the user in question and a countall number. The countall number is the amount of days holiday the person has taken so far. previous2009 looks at 20091 and gets the countall number then...

The question was 'Can this be done in code?' Would it save on using loads of queries? i.e. is it possible to put both queries into a few lines of vba code and still return the same figure into a variable? Thanks Martin

Hi, thanks for your reply. I have added that in but it still looks like I will need a query for previous2008, previous2009, previous2010 etc... Is there anyway to get round this? Thanks Martin

Hi all, I have the following problem which is driving me nuts and was wondering if anybody can point me in the right direction - please bear with me its quite a long one. I have the following query (a20091) SELECT holidaybookings.id, Count(holidaybookings.id) AS Countall FROM...

excellent thanks

Hi all, I have the following code: If Format(usersenddate, "YY/MM/DD") >= Format("1/1/*", "YY/MM/DD") And Format(usersenddate, "YY/MM/DD") < Format("1/4/*", "YY/MM/DD") Then Where the code says 1/1/* I want the * to be the current year. i.e. 2009 then 2010 etc... Can this be done...

thanks, i'll try this tomorrow and let you know. cheers

Hi, qry_countholidaysin2009 is a name of a query. when query is run on its own it returns the value 8. qry_countholidaysin2009 looks like this in sql mode: SELECT Count([holidaybookings].id) AS Countall FROM holidaybookings WHERE holidaybookings.id = forms!holidaymainbasic!child3!id...

Hi all, I have the following code: SELECT ([entitlement.entitlementfld] - [qry_countholidaysin2009]) as daysremaining FROM entitlement WHERE entitlement.id = forms!holidaymainbasic!child3!id; can this be done in a query and return a number? qry_countholidaysin2009 returns "8". So I would...

cheers, seems to work, many thanks.

Thanks for the reply, im not sure how to put this into context though. what does the bit ("SELECT @@IDENTITY AS LastID;") mean? and the showidentity? Thanks Swarv

I have the following code: SQLText = "INSERT INTO BaseData ([lastname], [firstname], [Title], [Department], [EmpTerm], [username], [sec]) SELECT '" & lastname & "', '" & firstname & "', '" & title & "', '" & dept & "', '" & EmpTerm & "', '" & username & "', '" & sec & "'"...