I have the following script which lists the query name and it's field names.I also want to list any expressions in the field names but am not sure how to achieve it.i.e the expression in a field of one query is.
The following script only lists the actual field name
Code:
Public Sub QueryFields()
' Read control source property of all controls in all reports.
' Note.. the debug window may not be big enough to display
I am getting the following error message in strsql:You tried to execute a query thats does not include the specified expression 'ScanDate=20140730 and SCanhour=8' as part of an aggregate function in the following strsql.
Code: Call func1("Z", 8) Public Sub func1(b As String, a As Integer) strsql = "SELECT Count(BatchNo) AS CountOfBatchNo, Sum(Envelopes) AS SumOfEnvelopes, Sum(Cases) AS SumOfCases, Sum(Pages) AS SumOfPages, ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') AS QueueNo " _ & "FROM jabberwocky " _ & "group By ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') " _ & "HAVING ScanDate=" & J & " and Scanhour=" & a & "" End SUb
I have got an unbound multiple list box called List44 (Row Source: query based on table tblAircraftCategory, Multi Select - Extended) that needs to be passing parameters to my main query called AircraftSearch2. The multiple choice list box have the following fields:
1. Piston 2. Turbo Prop 3. Entry Level Jet 4. Light Jet 5. Super Light Jet 6. Midsize Jet 7. Super Midsize Jet 8. Heavy Jet 9. Ultra Long Range 10. Helicopter 11. Air Ambulance 12. Cargo 13. Vip Airliner 14. Airliner
The user will use the form for selecting search criteria (the form is called SearchForm2 and has 5 combo boxes, 3 text boxes and one multiple choice list box).
I'm very new to access and need to modify (or coding a separate module) my query to include my multiple choice list box in my query?
So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.
I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter.
Here is some of my code:
Code: Option Compare Database Option Explicit Private Sub Form_Current() Dim oItem As Variant Dim bFound As Boolean
[Code] ....
Here's my current query in SQL:
Then finally how to I get the query to execute and create a report based on all of this?
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 looking for a bit of code for a module that will return the field name from an expression (that uses an array) instead of returning the value in that field.
Example: (Minimum is a working module that finds the smallest number in the array) Field1 = 34 Field2 = 53 Field3 = 21 Field4 = 62
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform 'Application.TempVars.Add "varcountryselect", "*" SELECT in the listbox "lstlocationsperproject" SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0) Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
=IIf([Details]="COST OF PRODUCT",[PerUnit]*[Quantity],0)
it keeps coming back with 0 even when the text says COST OF PRODUCT
I have also tried: =IIf([Details]="COST OF PRODUCT*",[PerUnit]*[Quantity],0) =IIf([Details] Like "COST OF PRODUCT",[PerUnit]*[Quantity],0) =IIf([Details] Like "COST OF PRODUCT*",[PerUnit]*[Quantity],0)
I'm using the expression builder to create it in a total box in the report footer, I have a box that gives me the total of the report using a sum and I need to show the cost of product separately to this as I need to use it in another calculation.
I am getting something wrong but I don't know what - date related.
Take this code for example:
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=" & varDate) > 0 Then MsgBox "Go Away", vbOKOnly Else Go on and do the real work End If End Sub
The DCount line is being completely ignored, no error message, it just cracks on as though there were no records found (but they are definitely there!) I have tried:
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate='" & varDate & "'") > 0 Then MsgBox "Go Away", vbOKOnly
But that throws up a Type Mismatch (which is what I would expect). Then I tried
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=#" & varDate & "#") > 0 Then MsgBox "Go Away", vbOKOnly
And it gets ignored again.
I am having similar trouble trying to open another form using the same field as the open argument, and getting similar results, so it is clearly me that is wrong.
The error I'm getting is "data type mismatch in criteria expression".
Private Sub BtnAddBooking_Click() Dim CustomerID As String Dim StaffID As String Dim intPos As Integer Dim strSQL As String Dim strSQL2 As String Dim BathValue As Integer Dim rst As DAO.Recordset Dim RemainingBookingExists As String Dim RemainingBaths As Integer Dim RemainingBathsExists As String
I'm currently in the process of moving from Excel to Access and am setting up a user table with 5 Fields (Full Name, First, MI, Last, Alias). I would like to only transfer the Full name and have the other field be calculated fields, so they automatically fill in. I was able to get the "Last" field to work, but the others are being tricky. Especially the "Alias" field which would need to take the first letter of the first name, MI letter and the entire last name, like (JPDoe).
The current format of my "Full Name" field looks like, (Doe, John P.) without the brackets of course. I'm new to Access, so I was trying to use the Expression Builder to do all of it, unless there is an easier way?
Expr1 is a field in a report that is bound to this query.
timeline.CompanyName doesn't usually have any data in it, and when it does it needs to be removed and manually input into companies. Company Name.Is it possible to make timeline.CompanyName output as italics? Otherwise we dont know which field is populating.
Code: SELECT [timeline.CompanyName] & [companies.CompanyName] & " (" & [companies.city] & ", " & [companies.state_id] & ") ~ " & [timeline.Title] AS Expr1, timeline.showitem, timeline.EntryId, timeline.EmployeeId, timeline.CompanyName, timeline.EmpStartDate, timeline.EmpEndDate, timeline.Super, timeline.CoPhone, timeline.Notes, timeline.Title, timeline.company_id, timeline.start_date, timeline.end_date, timeline.assignment, timeline.notes_staff, timeline.showitem FROM timeline LEFT JOIN companies ON timeline.company_id = companies.company_id WHERE (((timeline.showitem)="false")) ORDER BY timeline.end_date DESC WITH OWNERACCESS OPTION;
I have an asset database I am designing to manage our computer inventory and assets.
I am trying to get a DLookup to work with one of my forms that will auto-populate some of the fields depending on what is entered in to the ProductID field. For instance, Make, Model, Asset type...
My problem is that the string that returns contains special characters, specifically "#" and gives me the error message -
Run-time error '3075': Syntax error in date in query expression 'productID=EN371UA#ABA'.
My expression is definitely working, it just looks like it things it has something to do with date/time which it does not. Unfortunately, most HP equipment contains a # in the Product ID number.
Here is my expression -
Private Sub ProductIDCombo_AfterUpdate() Make = DLookup("Make", "productlist", "productID=" & [ProductIDCombo]) End Sub
Make is the field I am looking up from the ProductList table. The Product ID is the ID I'm looking up from the ProductList table to find the make. My problem is actually getting it to return the correct value of "HP or Dell or Lenovo". etc.
I have a searchable form that display information on agreements other companies have with us. If a company requests an inspection we have 30 days to go out and complete it.
I am trying to create a message box that will display all inspections that are due within 5 days when the form loads. No luck so far, only broken dreams.
Is it possible for message boxes to use expressions and display information that fits the criteria?
I have two tables in a query joined by a ID field. The problem is in the one table the ID fiels is a text format and in the other table its a number format so the query builder doesnt like that and gives me the Type mismatch in expression error.
There's no way around it though; i need it to be those formats in its respective tables for reasons i wont go into here for simplicity.
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
Simple this one but it's something I can't seem to get working so I thought I would ask as I'm missing something obvious.
What is the correct expression to use in a query to select records with particular dates. The database contains information on quotations and each record has a follow up date field and a status field.
The query should find records where the follow up date is today and the status is "Live".
I have =NOW() in the date field and "Live" in the status field but no records are being found even when I know there are some that match the criteria.
Good Morning. Have a quick question for the gifted.
First let me say I have inside my table Target Install, Received Date, and Type of Circuit
In the Query, I have created a column with the following statement. Due Date: [Target Install]-30 which works great to give me 30 days from the target install. The issue is that this statment is not correct for all products. So I need to understand how to build a formula for this in the query.
If [Type of Circuit] = "Branch Network Frame" than [Target Install]-30 or If [Type of Circuit] = "Class I" than Received Date+7
Could someone provide me with an expression that would always return data from last week? I've been playing around with serialdate and datepart but can't quite figure it out.
I have a query with Product, Product Directorate, Date Received, Date Delivered, and Date approved fields.
Product field criteria: "NAT CONV"
Product Directorate criteria: ([forms]![frmWhatDates].[cboPD]) or ([forms]![frmWhatDates].[cboPD is null)
Date Received criteria: (Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd]) or(([forms]![frmWhatDates].[txtRecvStart] is null) or ([forms]![frmWhatDates].[txtRecvEnd] is null))
and so forth
When I closed my query, and opened it up again it looked bizarre. Because of the Is Null it added criteria multiple times.. I don't know what's going on but this makes it hard if i want to edit it in the future.
Why did Access do this to my query? Is there anyway I can make this simpler and how?
What I have is a form that filters records according to what is input and it generates a report. If that date fields are left blank.. I want it to display all the records
Since I am using Domain lookup function in this field, it takes considerable time for this field to get populated on the form when I move from one record to another.
Can anyone tell me the syntax for me to move this expression inside the Form's Query so this can execute faster?
Please note that I need this query to be updateable as I have some other form fields that needs to be edited.
Hello all, I have ran into a bit of a problem with a database of mine that is for an architectural firm.I have 3 tables in total.They all have a relationship between them and the main table is ERGO.This table has general info about the work that the company has undertaken on behalf of the client as well as the total price.Now a house can have two predetermined prices one for the study of it and another for the supervision.These two pieces of info are divided into two fields in the main table.The other two tables have info on the various deposits the client makes for either the "study" or the "supervision" or both. What i want to achieve is to be able to print in a report the balances for all the clients that have (balance > 0) either for study or supervision.I have tried to make an expression in a query and use that in the report but i get an error when trying to run the query.On the other hand when I tried to do the calculation in the report itself the calculation is computes just fine but I have instead of one "output per house" I have multiple outputs as many as the deposits of the client. I have included a copy of the tables , if someone has any clue plz help me out :)
Hi all, I have a field that stores software versions as text. The version numbers can be simple (4.0, 5.0) or unknown or more complicated (4.5.200512, etc). The user wants to be able view data in a pivotChart without seeing interim versions- so 4.5.200512 would not show up but its data would be counted as part of 4.5. Anybody know how I can do this? thanx
