Hi - I am using the replace function in a query expression to remove spaces in a postcode field:
Replace([POSTCODE]," ",""))
However, this returns an error if the Postcode is left blank - as I am trying to match the postcodes from 2 queries the error is giving a type mismatch.
(I presume its this anyway)
I am thinking I need to use an Iff function deal with the instance of no postcode(i.e. NULL) but I can't get it to work.
When I drop a field into a form I'd like to take the Lable Box and put in into the Page Header section and leave the data field or control box in the detail field.Sometimes I have available "Remove Layout" function to split the 2 so I can manipulate them around.
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total ----------------------- 2 | 1234 | 80.00 ------------------------ 1 | 43526 | 20.00 ------------------------ > | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer) Dim SPSelect As String SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE" SPSelect = SPSelect & " ItemID = '" & ItemID SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
I don't get it!!!! I use it all the time. <=Date() should get all rows with no future dates...but I get this error on some of my queries...I've never got this error before... :mad: :mad: :mad: :mad:
I have used the Trim function in a query and am getting an error. At first I was concatanating several fields but have cut it back to the most basic form but still receive the error. The error says that the trim expression failed. Here is the code I used:
I made a function to calculate the difference in hours between two times.
Function TimeDiff(Time1 As String, Time2 As String) As Double 'This function is used to calculate the difference in hours between two times. 'It is capable of handling a finish time that is after midnight.
TimeDiff = 0
If Not IsNull(Time1) And Not IsNull(Time2) Then
If CDate(Time1) > CDate(Time2) Then TimeDiff = ((CDate(Time2) - CDate(Time1)) + 1) * 24 Else TimeDiff = (CDate(Time2) - CDate(Time1)) * 24 End If
End If
End Function I have a query where there are 2 sets of times in fields (Start1, Finish1, Start2, Finish2). When I add a calculated column with the following expression: Expr1: TimeDiff(Start1,Finish1) + TimeDiff(Start2,Finish2) it calculates in the query correctly.
However, when I made a new function that incorporated this function, it gave me the #error result in the calculated field. Function OrdinaryHours(Status As String, Start1 As String, Finish1 As String, Optional Start2 As String, Optional Finish2 As String) As Double
'Initialise OrdinaryHours = 0
'If employee is NOT a casual If Not Status = "CT" Then
If Not IsMissing(Start2) And Not IsMissing(Finish2) Then OrdinaryHours = TimeDiff(Start1, Finish1) + TimeDiff(Start2, Finish2) Else OrdinaryHours = TimeDiff(Start1, Finish1) End If
End If
End Function Does anyone know why this would #Error. I have the TimeDiff function initialising to = 0 before doing anything...I am stumped why it returns #Error when it should return 0 in the case of something going wrong.
I'm running an update query and I get this error - Function Sequence Error. (#0)
I have an excel spreadsheet that I imported into a table. I am trying to update the part table with date from the spreadsheet. The part id is unique and thats how the tables are joined. I only need to update 5 fields. The table I'm trying to update is a linked table.
I can't figure out what I need to do to make the necessary updates.
Hi, i know ive asked this in another forum, but i just read a thread on a query function which may be able to deal with this so thought id post it here:
the function i just saw:- Expr1: IIf([Sold],="","0",[Sold]) this does nothing to help my problem though so any ideas?
i have three fields; [boughtin], [sold] and [instock]. [boughtin] comes from one query and [sold] from another.
[instock] = [boughtin] - [sold]
because [sold] only has a value once an invoice has been created for that item, it means that if you try to view an item that hasnt been sold yet, you see how many have been bought in, but no sold value and then because of this, no stock value. (you just get an '#error' value in the stock box.
basically what i need is this: if both [boughtin] and [sold] are present, then the calculation is done and a [stock] value is shown. if only [boughtit] is present then the stock value simply shows the [boughtin] value.
[boughtin] is located on one subform (frm_stocksub) and [sold] is one another (frm_stocksubsub).
i need a code to put into the after update event of the main combo on the main form so that it detects this and then displays the approriate value once both queries have run.
a simple idea of what i want is: If [Sold] has no value Then [InStock] = [BoughtIn] Else [InStock]=[BoughtIn]-[Sold] End If
Obviously there is far more detail in terms of locating the particular text boxes, but i hope it gives you an idea of what i want.
I am working on a marketing database. I have two tables that are in use called tblListInfo and tblResults.
In tblListInfo the fields I am using are DropDate, ListName and Marketcode. In tblResults the fields I am using are JoinDate and Marketcode.
I am attempting to write a query that the end result will display the number of records within the 1st week of the promotions drop date. For every combination where the ListName value AND the DropDate value are unique, I want to count the number of records in tblResults that have JoinDate BETWEEN DropDate AND DropDate + 6.
I tried writing the code for this in pieces starting with:
SELECT DISTINCT tblListInfo.[ListName], tblListInfo.[DropDate] FROM tblResults INNER JOIN tblListInfo ON tblResults.[Marketcode] = tblListInfo.[Marketcode]
This gave me my first ideal part of the output I was seeking. I listing of all the possible marketing lists used with duplicate names occuring only when there were different drop dates. for example:
Listname Drop Date List - 1 1/1/08 List - 1 1/15/08 List - 2 1/1/08 List - 2 2/5/08
From there I wanted to modify the process to include a third field that included the # of records in tblResults that have the JoinDate field BETWEEN tblListInfo.[Drop Date] AND (tblListInfo.[Drop Date]+6). The ideal output would be like this:
Listname Drop Date Week 1 List - 1 1/1/08 15 List - 1 1/15/08 8 List - 2 1/1/08 32 List - 2 2/5/08 12
My attempt at getting to this result is below:
SELECT DISTINCT tblListInfo.[drop date], tblListInfo.[ListName], count(tblResults.[join date]) AS [Week 1] FROM tblResults INNER JOIN tblListInfo ON tblResults.[MarketCode] = tblListInfo.[MarketCode] WHERE tblResults.[Join Date] BETWEEN tblListInfo.[drop date] AND (tblListInfo.[drop date]+6)
I receive an error at this point saying: "You tried to execute a query that does not include the specified expression 'ListName' as part of an aggregate function."
Can anyone offer suggestions/corrections to my approach and logic?
Hello I keep getting the error after I copied a form and saved under a different Name ++++++++++++++++++++ A problem occured while Microsoft Access was communicating with OLE Server or ActiveX control.
There may have been an error evaluating the function , event or Macro
+++++++++++++++++++++++++ Strangely, I keep getting this error when loading and clicking on a control in the ORIGINAL form while the copy seems to work without errors.
I have deleted some tex box controls from the first form and added a few new ones in the copy.
I have tried a copy of the database by deleting the New form, but still get the same error.
I have run DEBUG on both form codes and everything seems fine.
I have checked the refrences and find none missing.
Is there any way to resolve this issue. Or am I doing something fundamentally wrong by copying and modifying the form ?
I have used access to create a database for my web site which I use ASP to connect. I've written a function in Access which takes a product description and shortens it down...
Public Function GetShortDesc(strInput As String)
Dim x As Integer Dim tempstr As String Dim checkstr As String x = 100
tempstr = Left(strInput, x) If Right(tempstr, 1) = " " Then GoTo stringfound Else Do Until checkstr = " " x = x + 1 tempstr = Left(strInput, x) checkstr = Right(tempstr, 1) Loop End If
stringfound:
GetShortDesc = Left(strInput, x - 1)
End Function
And I have used this function in a query which works fine in Access, but when I go to my ASP page and try to return the field to my page I am getting an error...
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC Microsoft Access Driver] Undefined function 'GetShortDesc' in expression.
It looks like the ASP page isn't accepting my function, Can anybody help???
SELECT tblTempProject.project_number, tblTempProject.Project_name, tblTempProject.subsidiary, tblTempProject.division, tblTempProject.unit_number, tblTempProject.client_name, tblTempProject.resourcing_contact, tblTempProject.project_cat_type_code, tblTempJob.headline, tblTempJob.contact_name, tblTempJob.contact_staff_number, tblTempJob.contact_unit_number, tblTempJob.resourcing_team_email, tblTempJob.creation_date, tblTempJob.nominatio_date, tblTempJob.decision_date, tblTempJob.sar_status, tblTempJob.publication_level, tblTempJob.sar_number, tblTempJob.last_modified_timestamp, tblTempJob.last_updated_by, tblTempJob.requirements, tblTempJob.positions_filled, tblTempJob.closure_description, tblTempJob.other_inf, tblTempJob.description, tblTempJob.qual_exp, tblTempJob.Tag1, tblTempJob.Tag2, tblTempJob.Tag3, tblTempJob.Tag4 FROM tblTempJob, tblTempProject WHERE tblTempJob.project_number = tblTempProject.project_number AND tblTempJob.project_category_type_code = tblTempProject.project_cat_type_code AND tblTempJob.project_company_code = tblTempProject.subsidiary AND tblTempProject.subsidiary='NL' AND (tblTempJob.sar_status='FIR' Or tblTempJob.sar_status='HOL' Or tblTempJob.sar_status='PRO25' Or tblTempJob.sar_status='PRO50' Or tblTempJob.sar_status='PRO75') AND tblTempJob.project_company_code=[tblTempProject].[subsidiary] AND tblTempJob.project_category_type_code=[tblTempProject].[project_cat_type_code] AND tblTempJob.project_number=[tblTempProject].[project_number] AND tblTempJob.external_resourcing=-1 GROUP BY tblTempProject.project_number ORDER BY tblTempJob.sar_status;
When execute this query then I get the following error: "You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)"
When delete the line: GROUP BY tblTempProject.project_number, then it works. I don't know what I must do to get this right so that I have every project one time in the result.
I am trying to run a query with a field called "Age". The field Age is supposed to determine the age of the record based on what's in the remaining amount field and the cash date, the disp date, or the run date of the download (typically the last day of the month). So my expression for the Age column is as follows:
AGE: IIf(Min([REMAINING AMT])=0,[CASH DATE]-[DISP DATE 1],[CASH DATE]-#1/31/2005#),
which is saying of the remaining amount = 0, then calculate the age based on the difference between the cash date and the disp date, otherwise, calculate the age based on the cash date and the run date of the download, right?
Well, when I run the query, I get the following error message:
You tried to execute a query that does not include the specified expression 'IIf(Min[REMAINING AMT])=0,[tablename].[CASH DATE]-[DISP DATE 1],[CASH DATE]-#1/31/2005#)' as part of an aggregate function.
What does that mean and how can I get this to work? My "Total" line is set to "Expression". I even tried it with "Group By" and it gave me another error message. Please help me!
We use Microsoft Access databases to enter test data and eventually generate reports.
On 1 computers i get the following error when opening the report:
"Function is not available in expressions in query expression 'Left(.....)"
I figured this is a reference problem so the next thing i did was to pinpoint what reference was causing this error.
It turns out it is the Microsoft DAO 3.6 Object Library. Simply removing the reference and adding it again fixes it and allows you to open reports just fine.
The problem is though, this message comes back every now and then. Which is getting annoying and the person who is making the reports is about to throw his computer out of the window.
I'm trying to delete a file in a network location. Sometimes the file is there, sometimes it's not, so I'm using the following code:
Code: strPath = "etwork driveMy Folder" strFilename = "MyFile.doc" If Not Dir(strPath & strFilename) = "" Then Kill strPath & strFilename
Occasionally, I get a runtime 52 error when the file doesn't exist. I don't understand why I'm getting this when, if the file doesn't exist, the Dir function should return "".I searched the forums for Runtime 52, and didn't find anything pertaining to this.
My query includes a few new columns I have added and I need to use GROUP BY so each new column is grouped per store.
When I try to use: GROUP BY newColumn1, newColumn2, newColumn3;
I get the following error: 'You tried to execute a query that does not include the specified expression 'Store Nbr' as part of an aggregate function'
All I want to do is have each new columns distinct value show up once for each particular store, not repeated multiple times. When it is repeated multiple times, it also shows for each store department which messes up the metrics of the report.
All I want to do is run a make table query (or append) and show the user how many records were processed. If I try to set recordset equal to the qdf.Execute I get the Compile error "Expected Function or variable".
I'm not sure what I'm missing...I've searched numerous threads and tried various combos of the execute method.
strquery = "qryEmailGenerate" Set db = CurrentDb Set qdf = db.QueryDefs(strquery) Set rs = qdf.Execute txtStatus = "Number of email recs: " & rs.RecordCount & vbCrLf
I am completely at a lose here. I have a LARGE sql query that was written before my time and how have the task of customizing it a little. It will run perfectly in Advisor Office (the program it was written for which uses access as the back end db). But if i try and create a query in access directly i get the following error:
You tried to execute a query that does not include the specified expression 'IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))), IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type =' as part of an aggregate function
the full IIF statement that is causing it to fail is:
The WHOLE SQL query spans over 4 pages of A4 so i will only put it at the bottom (hoping its not needed to see the error. There are a few unions so i will put the first one in here to show what it is doing.
SELECT IIF (Policies.Owner = 0, clients.partnersurname + ", " + clients.partnerforenames, clients.surname + ", " + clients.forenames) AS Name, Policies.PolicyNumber, Schemes.SchemeName, Managers.FullName AS Manager, CommnPremiums.CommnPremiumDate, IIF (CommnPremiums.Type = 1, 'Initial Premium', IIF (CommnPremiums.Type = 2, 'Increment', IIF (CommnPremiums.Type = 3, 'Single Premium', IIF (CommnPremiums.Type = 4, 'Annual Renewal', IIF (CommnPremiums.Type = 5, 'Value Based', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' '))))))) + IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type = 1, ' - Non Indemnity', IIF (CommnEntries.Type = 2, ' - Indemnity', IIF (CommnEntries.Type = 3, ' - Renewal', IIF (CommnEntries.Type = 4, ' - Level', IIF (CommnEntries.Type = 5, ' - Fund Based Trail', IIF (CommnEntries.Type = 6, ' - Level Indemnity', ' ')))))), ' ') AS PremiumType, CommnEntries.DueFrom, CommnEntries.DueTo, SUM (Matches.Amount) AS TotalMatched, CommissionCredits.CreditPC AS Credit, Consultants.FullName AS Consultant, ROUND ((CommissionCredits.CreditPC * SUM (Matches.Amount) / 100), 2) AS CommissionMatched, Cheques.Amount AS ChequeAmount, Cheques.ChequeDate, IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))), IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type = 4, 'Renewal', IIF (CommnPremiums.Type = 5, 'Trail', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' ')))))) AS CommissionType FROM ((((((((Clients LEFT JOIN Policies ON Clients.ClientRef = Policies.ClientRef) LEFT JOIN Schemes ON Policies.SchemeRef = Schemes.SchemeRef) LEFT JOIN CommnPremiums ON Policies.PolicyRef = CommnPremiums.PolicyRef) LEFT JOIN CommnEntries ON CommnPremiums.CommnPremRef = CommnEntries.CommnPremRef) LEFT JOIN Matches ON CommnEntries.CommnEntryRef = Matches.FKeyRef) LEFT JOIN Cheques ON Matches.ChequeRef = Cheques.ChequeRef) LEFT JOIN Managers ON Cheques.FKeyRef = Managers.ManagerRef) LEFT JOIN CommissionCredits ON CommnEntries.CommnEntryRef = CommissionCredits.FKeyRef) LEFT JOIN Consultants ON CommissionCredits.ConsultantRef = Consultants.ConsultantRef WHERE Matches.FKeyType = 1 AND Cheques.FKeyType = 1 AND Cheques.ChequeDate BETWEEN #01-Jan-2004# AND #31-Dec-2004# AND CommissionCredits.FKeyType = 1 AND CommissionCredits.Type = 1 GROUP BY IIF (Policies.Owner = 0, clients.partnersurname + ", " + clients.partnerforenames, clients.surname + ", " + clients.forenames), Policies.PolicyNumber, Schemes.SchemeName, Managers.FullName, CommnPremiums.CommnPremiumDate, IIF (CommnPremiums.Type = 1, 'Initial Premium', IIF (CommnPremiums.Type = 2, 'Increment', IIF (CommnPremiums.Type = 3, 'Single Premium', IIF (CommnPremiums.Type = 4, 'Annual Renewal', IIF (CommnPremiums.Type = 5, 'Value Based', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' '))))))) + IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type = 1, ' - Non Indemnity', IIF (CommnEntries.Type = 2, ' - Indemnity', IIF (CommnEntries.Type = 3, ' - Renewal', IIF (CommnEntries.Type = 4, ' - Level', IIF (CommnEntries.Type = 5, ' - Fund Based Trail', IIF (CommnEntries.Type = 6, ' - Level Indemnity', ' ')))))), ' '), CommnEntries.DueFrom, CommnEntries.DueTo, Cheques.Amount, Cheques.ChequeDate, IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))), IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type = 4, 'Renewal', IIF (CommnPremiums.Type = 5, 'Trail', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, ‘Transfer’, ' ')))))), Consultants.FullName, CommissionCredits.CreditPC
If I changed the IIF statement to:
'X' as commisiontype
it all works great but i need this information and its driving me mad. I can not see why it doesnt work within Access.
Any and All help would be great.
thx
Twiggy
I have attached the FULL sql query if you want to look at it all.. it hurts my head to read it all ;)
I keep getting Expected: = when trying to write a new Function in a module in my Access Database. Can't understand it.
Even some code as simple as follows causes this.
Function OpenInterface() Dim variable As String Set variable = "help" Debug.Print(variable) End Function
It doesn't cause error if I include an argument, though I want to to create 2 tables from some existing tables, but not to return anything and there are no arguments. Should I be using a Sub? In which case... can someone help out in how I go about writing the opening and close lines.
I have the following in a query to assign a sequence number to each record:
(SELECT Count(*) FROM [qryPTLActivity] AS T WHERE T.EmployeeID=[qryPTLActivity].[EmployeeID] AND T.PickDateTime <= [qryPTLActivity].[PickDateTime] AND T.PickAisle=[qryPTLActivity].[PickAisle])
I have used this in other databases without issue but for some reason this time I keep getting an "Invalid Argument for Function" error.
SELECT (sum(d.cartonsshipped)/m.ctnperskid) AS PalletCount, itemnumber, shipdate FROM orderdata d inner join itemmaster m on d.itemnumber = m.itemnumber
GROUP BY d.itemnumber, d.shipdate HAVING (sum(d.cartonsshipped)/m.ctnperskid) > 0 ORDER BY d.shipdate, d.itemnumber;
and not get the aggregate error (3122). I am guessing it's giving me the error because i try to do a function in the select area.
