Queries :: Last ID From TblTest Cannot Work With DLookup
Jan 13, 2014
I read a lot about retrieving the LAST generated Identity from an SQL-server table. Everythings seems to work, but I get a wrong result.It seems that SELECT SCOPE_Identity does not work with access when working with such a code for example:
strsql = "INSERT INTO tbltest ( myDate ) " _
& "SELECT '" & dteDate & "' AS dte; SELECT SCOPE_IDENTITY() AS TestID; "
Call SQL_PassThrough(strsql,"myTest_PT")
2nd parameter is an existing PT-Query with the connection and return values=yes. If no 2nd para then there is no resultset, only the insert.
So I changed it to:
strsql = "INSERT INTO tbltest ( myDate ) " _
& "SELECT '" & dteDate & "' AS dte; "
Call SQL_PassThrough(strsql)
Set rs = CurrentDb.OpenRecordset("select @@Identity as TestID from tblTest")
lastID = rs("TestID")
This brings me to my big surprise an Identity from ANOTHER (!!) table, but not the last one from tbltest. The code is running for test reasons in another modul and not in the one I creating the received ID.
As I have to get for sure the last ID from tblTest I cannot work with DLookup, as in my multisuer App this is not sure enough.build a construction where I get the last ID from the table where I just made my insert.
I'm trying to build a form that Pulls data from several tables, though will update only one. I'm having issues right now with a field that is supposed to draw from a query. I made a query that is supposed to concatenate different fields in another table, look for duplicated words and remove them, but although that isn't working yet, that's not my issue. My issue is that the DLookup in the form isn't even trying to look at the query. The query prints out workshop IDS and a concatenated field right now, so there is data to pull.
=DLookUp("[Expr1]","[Posting Title Query]", "[WorkShop_ID] =" & [WorkShop_ID])
Query name: Posting Title Query Query Reference Field: WorkShop_ID Query Data Field: Expr1
Form Name: Sessions Form Reference Field: WorkShop_ID Form Table: Session List
basically am creating a booking system, i have a add a room form. my form should check whether i already have a room number in my table, which works when the form is filled in. however when my form is null, then i press add new room button, i get this error rather than " please fill your form in"
Error: runtime error '3075' syntax error (missing operator) in query expression 'Room Number ='.
room number is a number field, integer but has primary key. i cant keep autonumber, as my requirement is to add new room number, but the roomnumber has to be unique.
here is the dlookup;
If DLookup("RoomNumber", "tblRooms", "RoomNumber = " & Forms!RoomPackages!txtRoomNumber) > 0 Then MsgBox "This number already exists." Else
I am not real sure whether or not this can be done with a query, but here goes. I have a query randomly selecting the top 6 from a selected group.
SELECT TOP 6 tblConsortium.ContactID, tblConsortium.RandomID, patients.LastName & ", " & Patients.Firstname AS [Employee Name], Contacts.Company AS [Company Name] FROM patients INNER JOIN (Contacts INNER JOIN tblConsortium ON Contacts.ContactID=tblConsortium.ContactID) ON patients.PatientID=tblConsortium.PatientID WHERE (((tblConsortium.ContactID) In (SELECT ContactID FROM tblConsortium GROUP BY ContactID HAVING Count(*) <25))) And (((tblConsortium.PatientID)=Randomizer())=0) ORDER BY Rnd(IsNull(tblConsortium.patientID)*0+1);
What I would like to do now is: The randomly selected Employees need to have a randomly selected TEST. There are only 3-Test to choose from. I have placed the TESTS in a separate table and entered 20 of TEST1, 4 of TEST2, and 1 of TEST3. I then set a query randomly shuffling these items. I need these TESTS to randomly be assigned to the randomly selected Employees above.
I would greatly appreciate any thoughts or help… Thanks Enviva
I need to do the price in table [price] multiply by 1.20 if the price is higher then 150. If the price is between 75 and 150 it have to multiply by 1.25. Continue... continue... continue...
The records that I'm working with are pulled weekly from another db, which has been modified by a boatload of folks, over the years. Every one of them had their little ways of doing things. The data in "Product" field isn't always entered the same way. For example: SS_11_0101__Z2 and SS 11__0101_Z2 and SS 11 0101 Z2 are all the same product, just apparently selected from different drop down boxes which (of course) are usually slightly different, usually a problem with the underscores.
This is causing me to miss some listings when I run my queries. Fixing the main db is NOT an option. What I need to do is figure out how to find any "_" (underscores) and replace them with a single space, in my db.
I run some searches here, but haven't found anything that triggered an "AHA!" moment. Anyone have any recommendations about how to tackle this? Update query, Replace() function or maybe a macro?
As always, thanks in advance for any suggestions you may have.
I have an access 2003 database which holds data for lorry loads of timber delivered to different places at different prices and by different hauliers who get paid different ammounts.
I have tables that hold the prices for both haulage (sorted by the delivery location and haulier) and product price (sorted by the delivery location and the haulier.
I have two almost identical queries which give the haulage price for a particular load and the product price for a particular load.
Only the haulage prices seem to be returned - i have checked the settings and relationships in each of the tables invlovled and they appear to be that same.
Why would one query return the value and the other not???
I have a problem where I can create queries in code using functions such as Left() and they will work fine on my clients machines with a complied MDE file but if I try to use the same function in a saved querie they get an error: "Function is not available in expressions in query expression..."
The Queries work fine on my machine but not on those using Access Runtime. From my research it appears to be a problem with them not having the correct Reference on their machine. If that is true then which Reference do they need and is there away of installing that Reference by code?
I have a database with shifts in for staff. They have a bunch of times in and times out over a four week period. I have gotten an average weekly amount of hours for each staff member based on this but I need another equasion to work out their leave entitlement. It breaks down like this...
Average weekly hours x 5.6 x number of days working in this period (ie start date and end of financial year day count) divided by number of days in the financial year (ie 1/4/2015 - 31/3/2016 day count)
I'm just wondering of a way to do the day count based on me keying in the start date of the staff member (default 1/4/2015) and that access can work out the days in that financial year left and the actual days in that financial year.
It sounds simple enough but I want to get it to automate based on my start date.
i have an access table.i m using Dlookup function for select data in dlookup , i want to sel field as combo box value, and i want when i change combobox value by select from list ,dlookup result will be change .
I am trying to use a Like statement in a VBA dlookup, as some of the fields in a third party db i cannot manage are variations of fields in a table i need.
I have 2 tables, one called Addresses and Extended Areas.Within my imported orders I have addresses with postcodes and countries. My Extended Areas table has Country, zip_low, zip_high and extended area with a Y.I wish to check certain postcodes from a particular country between zip_low and zip_high, which gives Y if it is indeed a Extended area.
I have 3 queries that I need to join. the 3 queries work on their own. They are all similar to below
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1 FROM ASTDATA ORDER BY Rnd([ID]);
But when I join them, like below, the data doesn't change. Does the rnd function work with a union query?
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1 FROM ASTDATA UNION SELECT TOP 5 BSTDATA.[ID], BSTDATA.[Weight], BSTDATA.[StockCode], BSTDATA.[CurrentQty], Rnd([ID]) AS Expr1 FROM BSTDATA UNION SELECT TOP 5 CSTDATA.[ID], CSTDATA.[Weight], CSTDATA.[StockCode], CSTDATA.[CurrentQty], Rnd([ID]) AS Expr1 FROM CSTDATA
I have a query that uses a checkbox from a form as a criteria. in the table the values are stored as 0,-1. if i run the query with the checkbox false, the query works and filters correctly, same if the box is null. But when I check the box true, no records load. It worked in an older version of my database so i am not sure why when i copied it over it doesn't work. I even tried a combo box with values of 0, -1 and got the same results.....
I am attempting to use a form field as the source for a query filter criteria. Everything works fine if I simply use an "if equal" filter condition. As soon as I try a "like" condition, nothing works.
I created a test table with just one column (fld1). The table contains three records with the following values: BRDODS, BRD, TLAODS.
The following hard coded query returns two records, as it should.
SELECT Table1.fld1 FROM Table1 WHERE (((Table1.fld1) Like 'BRD*'));
I also created a test form (Form1) with just one text field (Text0). My intent is to soft code a criteria value via the form field instead of hard coding the query, as above. When I populate the form field with BRDODS, the following soft coded query returns one record, as it should.
SELECT Table1.fld1 FROM Table1 WHERE (((Table1.fld1)=[Forms]![Form1]![Text0]));
When I enter LIKE "BRD*" in the form field, no records are returned. I should get two records, just like the hard coded query above.
I've tried all variations of the LIKE statement in the form field, but nothing works.
I've got three fields - date_time, # of issues, issue reasons
I want to summarize these by work week.
So,
WW....... # of issues ...............................issue reasons 1 ..........<sum of all issues for the week>..list of all reasons entered 2 ..........<sum of all issues for the week>..list of all reasons entered 3 ..........<sum of all issues for the week>..list of all reasons entered 4 ..........<sum of all issues for the week>..list of all reasons entered 5 ..........<sum of all issues for the week>..list of all reasons entered
I know how to get the WW part - I do the datepart("ww",[Date_Time] for the expression. But how to write the query to do the other 2 parts, I'm lost.
TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date FROM [rpt LEAD TYPE SUCCESS RATE2] GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;
I get valid results.
When I run the form it prompts me 3 times for each start date and end date. Then I get the following error :The Microsoft Access database engine does not recognize '' as a valid field name or expression.
I am new to access but understand how to do a simple query. in this case i have 4 fields in my query. I only want to return work orders where the only SN's for that work order begin with 600 or NEX. when i use the like button it gives me all those work orders, however it doesn't exclude the work orders with other types of SN's. is there a formula i can use that says return work orders that only contain this type of SN?
I have a table that will have ITEMID as unique field but location could have save values. I tried using DLOOKUP to look at the value for the previous record to see if I need that value is same as current, however, since my data query are text formats, I am unable to. I get a message of mismatch data type. apply my query to look at sorted table by Location and ITEMID and if previous location = location, then my area=0?
I am trying to get a wildcard search to work with a form.
I have a query, in which the criteria is:
Like "*" & [Enter a word] & "*"
That works fine. I enter a word, and I get the few records in which the word appears.But if I try to replace [Enter a word] with a word entered on a control on a form, it doesn't work - I get all the records. This is my code:
tblmntlyalloc (Three fields and 12 rows i.e. one row for each month) 2015mnth, 2015allocation, 2015wrkdays jan 98 20 feb 93 19 etc etc etc
So in order to handle seasonality of sales, the sales department is given 1,200 points. you can then allocation any number of points (75 - 125) per month. But the total number of points must equal 1,200. So I created a table with the monthly allocation and workdays. I could hard code the % and work days into the formula like this
And this does work very well to get me my daily sales amount per month/wrkday
However I know sales will change the monthly allocation (still totals 1200) and HR could even have a change to the holiday schedule. So I want to utilize the table.
So my query brings in both tables, but they are not linked. And this is my formula.
