Here is my VBA Dlookup Code:
Code:
Public Sub test()
Dim frm As Form
Set frm = Forms!StationLevelSummary
[code]...
At the bottom im printing the content of the controls which are on my form. These should return 1 number, but for some reason it does not. Ive used this code many times but I cant figure out why nothing is being returned.
I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:
Code:
Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant
Dim priSubID As Long
Dim priLocusID As Long
Dim priResult As Variant
priSubID = nrPotSubID
[Code] ...
However, when I use it in query it only returns Case else - option and everything else is empty.
I'm trying to do a string compare between two variables. One string variable is part of an array (which I'm looping through), the other is passed to the function as an argument.the function should return the position of a field in the OrderBy string of a subform.Here's the VBA :
Code:
Private Function SortPosition(strOrderBy As String, strField As String) As StringDim arrSortedFields() As String
Dim i As Long
If Len(strOrderBy) > 0 And InStr(strOrderBy, strField) > 0 Then
arrSortedFields = Split(strOrderBy, ",")
[code]....
The weird thing is, the line in blue returns True when, by watching the variables, it appears that it should not, and returns False when it appears that it should?
arrSortedFields(i) = "[RandomField]"
strField = "RandomField"
arrSortedFields(i) Like "[" & strField & "]*" = False
arrSortedFields(i) = "[SomeFieldName]"
strField = "ADifferentField"
arrSortedFields(i) Like "[" & strField & "]*" = True
I would have thought the expression "[RandomField]" Like "[RandomField]*" should return True?And similarly the expression "[SomeFieldName]" Like "[ADifferentField]*" should return False?Have been using 'Like' for donkey's years and never seen it throw results like this before?
(P.S. The reason I need to use 'Like' rather than a straight = is to account for the possibility that a field may be sorted descending, and therefore to nullify the DESC keyword which may follow any given field...)
I have a Dlookup which returns the correct value but returns the text of the instead of a numeric value. I have triple checked that the fields are all numeric. I suspect there is something wrong with my Dlookup syntax as it is my biggest weakness. In the following syntax what could be wrong?
DLookUp("SSER","TABSSS",[grosspay] & " Between [TABSSS].[Minrange] And [TABSSS].[Maxrange]")
I'm using a Dlookup in expression builder and it's only returning the value of the first record rather than the criteria I specify.
View 6 Replies View RelatedI am having a problem with a Dlookup query. I want to achieve the following - I have 2 different tables
Demography Table - consisting of 2 fields, City and Region
For example
City Region
NYC NAM
The other Table totalflow consists of several field, one of the is flow from.
For example
Flow from
NYC
WAS
SEA
I want to do the following. Lookup the Flow from filed in table totalflow, and compare it to City in demography Table. If City = Flow from, return the value in Region. All are text fields.
I have tried following Query, but it only returns the same value for all rows. (from SQL view)
SELECT DLookUp("[Region]","Demography Table","[City] = '" &[Flow from]& "'") AS test
FROM [Demography Table], flowsize;
I'm working on my first report and am trying to get my textbox, tboJob to show a field value and not the ID. I have tried the following Dlookup in my control source with no luck.
Code:
=DLookUp("[JobName]","Jobs","ID=" & [tboJob])
JobName is the field I want to show from the table Jobs.
Hi,
I have a query with two table's in them. One is filled, the other is still empty.
Both table's are related to each other with the field "document number".
This was working fine in access 2000, now in 2003 it returns zero results.
I am starting to think it is because one table is empty it returns nothing.
How can i adapt the query criteria or something else that it shows the results from table one, while table two can still be empty.
One table holds the documents and all information about it, the other holds the information wich document is referenced on wich document. Now since i can have documens which don't reference to a document, i still want those in the list.
Anybody any idea?
Thanks
I have a table listing a number of cell phone products and their carrier along with their status' in the production process. I want the query to return the most recent status for each product for each carrier. However, out of the 20+ products that I'd like the most recent status for for each carrier, it's only returning about 12 products of the most recent status for one carrier.
Any help would be much appreciated! Leave a message if you need more clarification.
Thanks!
Item: Access app front end for an SQL database (production tracking/job logging utility)
Problem: The app has a main search page that allows job entries to be located and opened by varies criteria (job#, job name, etc). I can currently run multiple searches on different criteria successfully but if I open a job window(which has been returned as result of my search) and then close that job window to do a new search my search function will no longer return any results(other then “Calculating….”) unless I close out the access app and reload.
Thanks in advance for any ideas that can point me in the right direction to hunt down my bug.
Hi everyone,
I'm having an issue with a query I created. When run the query requests an Artist Name. I enter this and it returns one result. However in actual table ther are two results for that artist. The only difference is that for the record that doesnt appear the field labelled "Gallery" has a zero value.
I have checked the table and the gallery field is not set to a required field so I dont understand why it wont show it as a result of the query.
Any ideas?
Hi all:
running access 2000. I have a query that I selected to return the Top 1 record sorted descending on the date so it would pick the most recent entry. I did it by selecting Top 5 from the drop down box in query design mode and then changed the 5 to a 1 using SQL View from qry design.
SELECT TOP 1 Class.CourseID, User.UserName, Class.TrainerID, Class.ClassLocationID, Class.ElapsedTime, Class.ClassDate, Class.Comments
FROM [User] INNER JOIN Class ON User.UserID = Class.UserID
ORDER BY Class.ClassDate DESC;
It still gives me as many records as are entered on the system. How can I get JUST the most recent record entry?
thanks:confused:
I have built a Project/Resource database for our department, and I am having an issue with 3 computers in the group. First, all of our computers are setup the same (as far as I can tell), however, there are 3 users that have the same issue...when selecting an item from a dropdown, it should populate the dropdown below it with a new list, based on the first dropdown. It is only on certain forms, and only happens on these three computers. If the users log into any other computers, it works fine. I know it's not alot to go on, but does this sound familiar to anyone? I'm at a complete loss!
View 5 Replies View RelatedI have built a query that should append to another table. Please see SQL below -
INSERT INTO tblMyBackupContent ( LoginName, Title, Type, Code, [Date Assigned], [Date Started], [Last Accessed], Progress, [Date Completed], [Time Spent (min)], Score, Result )
SELECT tblConentImport.LoginName, tblConentImport.Title, tblConentImport.Type, tblConentImport.Code, tblConentImport.[Date Assigned], tblConentImport.[Date Started], tblConentImport.[Last Accessed], tblConentImport.Progress, tblConentImport.[Date Completed], tblConentImport.[Time Spent (min)], tblConentImport.Score, tblConentImport.Result
FROM tblConentImport
WHERE (((tblConentImport.LoginName)="iderand"));
When I run this query, it shows that 41 records are going to be appended. I click on yes, but when I go into the table to view the appended records they do not show.
Please help me.
I have a db with 2 tables, one containing basic info FirstName, Surname, YearGroup, and another table with other data. I imported a list of names into the first table, firstname and surname only were imported, other field was left empty, other table was also left empty. I made a simple select query to pull all records from both tables and i get no results at all. None of the imported names show on the query results. what I am missing?
View 2 Replies View RelatedI have the following two querydefs. Here is the SQL;
qry1
SELECT [dtFind], tblData.dtReading, tblData.dblValue
FROM tblData
WHERE DateDiff("n",[dtReading],[dtFind]) Between 1 And CInt([intMins]);
qry2
SELECT [dtFind], tblData.dtReading, tblData.dblValue
FROM tblData
WHERE dtReading Between DateAdd("n",-1*[intMins],[dtFind])
And DateAdd("n",-1,[dtFind]);
[dtFind] and [intMins] are parameters.
I use the querydefs is VBA code as such
Dim db As DAO.Database
Dim rstDataSQL as DAO.Recordset
Dim qdfData As DAO.QueryDef
Dim strQdef As String
Set db = CurrentDb()
strQdef = "qry1" ‘or qry2
Set qdfData = db.QueryDefs(strQdef)
‘Set values of parameters
qdfData![dtFind] = dtDate
qdfData![intMins] = intMins
Set rstDataSQL = qdfData.OpenRecordset
qry2 executes significantly faster than qry1, but I am having issues getting the correct results.
If I set [dtFind] = 12/28/2005 10:47:00 AM, both queries work fine. The last returned record has a value for dtReading of 12/28/2005 10:46:00 AM.
However, if I set [dtFind] = 12/28/2005 10:48:00 AM, only qry1 returns the right records. qry2 will not return the record with dtReading = 12/28/2005 10:47:00 AM, but qry1 will.
:mad:
I changed the Between statement to “Between DateAdd("n",-1*[intMins],[dtFind]) And DateAdd("n", 0 ,[dtFind])” to see what happens. As I expected, records where dtReading = 12/28/2005 10:47:00 AM and 12/28/2005 10:48:00 AM are returned.
The data should be in increments of 1 minute, although there are periods where data is missing. None of the dates have values like 12/28/2005 10:47:01 AM, i.e. seconds value is always 0.
I’ve tried adding “PARAMETERS [dtFind] DateTime, [intMins] Short;”, and also using CDate(DateAdd()) without any luck.
Whether I am using the queries using VBA/DAO or user input to set the parameter values, the results are the same.
What am I doing wrong?
:confused:
I am using the following:
SELECT CPL_CLIENTS.CODE, CPL_CLIENTS.FULLNAME FROM CPL_CLIENTS
WHERE (((CPL_CLIENTS.FULLNAME) Like "*" & Forms!frmSafeKeep_AddClient_SearchDesig!txtSearchT ext & "*"));
As the control source of a listbox, so that the user can enter something in "txtSearchText", and the listbox will be narrowed to only those those clients that have a matching part of their name.
The problem is that the user enters, for example, "duncan", and the list returns nothing, when in actual fact there are 5 matching records. If the user enters "Duncan" (note the capital letter), they get all 5 records as they should.
If memory serves, access should only be case sensitive if you tell it to be - and this is working fine on other forms.
The only difference between them is the data source. Other forms are getting data from linked tables in the backend. This form is getting it from a linked Oracle table.
I have a query which pulls up results based on the value of a text box. All works well except one field has a DOB (date) and if I search say for " /1976" I get no results, however search /76 and all the people with that year of birth show then show up. The date is stored in the dd/mm/yyyy format.
I'm using the like [forms]![clients]![searchfield] in the criteria of the query to bring up results.
I have two tables Contacts & Allowance. There is a relationship defined between the two based on Contact ID (primary field/contacts - foreign key/allowance). The Contacts table has Firstname + Surname. I have produced a query based on certain fields in both tables however the result is totally pulling incorrect info.
View 11 Replies View RelatedHi there,
I'm working on a select query that I have created through the visual query design window in Access 2003, I have multiple criteria but the query is not filtering the results properly.
I'm selecting only records :
where the DeliveryCountry field is within the EU - works
where the ProductID does not contain Repair, Upgrade or Rpr - doesn't work
where the Demo/Sale ID is 2 - works
I've viewed the code in SQL view but I still can't locate the problem, though I suspect it lies in the treatment of ANDs and ORs.
The SQL is pasted below (I have reformatted with indentations so I hope it is easier to read).
One thing to note: the reason the countries list is split in two is because Access did this automatically when I had one long list. Is there a maximum length for WHERE clauses?
Thanks in advance for your help!
Abby
SELECT Orders.ShipDate, Products.[Standard Tarriff Number], [Order Details].[Quantity]*[Order Details].[unitprice]*(1-[discount])*(1-[special discount]) AS [Line Total], [Order Details].Quantity, Orders.OrdDeliveryCountry, Orders.OrderID, [Order Details].ProductID
FROM Products RIGHT JOIN (([Demo/Sale] RIGHT JOIN Orders ON [Demo/Sale].[Demo/SaleID] = Orders.[Demo/SaleID]) LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Upgrade")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Repair")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
AND (([Order Details].ProductID) Not Like "*Rpr")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Upgrade")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Repair")
AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")
AND (([Order Details].ProductID) Not Like "*Rpr")
AND ((Orders.[Demo/SaleID])=2))
ORDER BY Orders.ShipDate DESC;
I have a switchboard which runs a google style search query to find people based on their first name, surname, date of birth or NI number on clicking the search button. It uses the following criteria in the query Like "*" & [Forms]![SearchF]![Firstname] & "*" for each of the above fields.
This works perfectly until someone inputs a record that doesn't have anything in one of the fields. ie, full name and DoB, but no NI number. When a record is entered in this way, the table stores the record but the search query cannot find it.
What is the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.
I want my query to calculate a date based on:
1) A Type field from my table
2) A calculated date based on other query fields.
My query has the following fields:
[Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]
I want to create a calculated field for [Projected Action 2] that says:
If [Type] is "A" then
if [Action 2] Is NotNull, Null
if [Action 1] is NotNull, [Action 1] + 10 workdays
if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays
if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays
[Code] ....
I would like to keep using nested IIf but I keep running into problems and I thought that there is probably a better way.
Multi search wont return results: Obviously I have my table and search form. But have hit a problem that is probably simple to overcome, but for the life of me I cant see it.
The small peice of code I have used = forms]![searchF]![name1] I have also tried Like"*" & forms]![searchF]![name1] & "*" still not working. The qurey accompanying form (searchF) works to displaying searchF form unpopulated which I believe is correct. I should at this stage be able to fill out the one table heading (name1) where the query should return a result in the (searchf) form but it does not return.
The tables, forms and quries, have all been saved and closed and re opened many times
The form (searchF) propeties have been checked and double checked. I have even built a test table but the same things happen as my main table.
I am building a prototype database and currently has two records in it (I know for certain they are not duplicates). Attached is the relationship and below is the query. The query keeps returning the correct results but twice. (Duplicate results.)
SELECT DISTINCT WeekdayName([Weekdaynumber],False,1) AS Weekday, [Master Data].Date,
[Master Data].Machine, [Master Data].Shift, [Master Data Query].[Master Data_Product Code],
[Master Data Query].[Cases/Pallet], [Master Data].[Expected Pallets to Produce],
[Master Data].[Actual Pallets Produced], [Master Data].[Damage (Units)],
[Code] .....
I have an unbound combo box that I set up in a form using the combo box wizard in Access 2010. I selected the option that says, "Find a record on my form based on the value I selected in my combo box." The problem is that the combo box will not always display the correct record. It seems to only display the first record of a group of similarly named records in a table.
Example:
Code:
Last Name First Name SSN
Smith Alex 123-45-6789
Smith Jane 234-56-7891
Smith Mary 345-67-8910
If I select Smith Jane, the record for Smith Alex will display. I know that this has something to do with the bound column property, but I am unsure what to do to fix the issue.
What I have tried: I tried setting the primary key as the first field, but then I could only search by the primary key, which is unrealistic for this database. Users will be searching based on last name. How do I make the combo box select whichever record I select from the drop down list?
I have a parameter query that contains information on a list of people and contains 3 checkboxes: alumni, parent, business
In this query, I am trying to use parameters to filter the results based on these three fields i.e.
true, false, true would return all records where either alumni, business or both are true, and parent can be either true or false.
false, true, false would return all records where only parent is true, and the other fields do not matter.