how I can make an Array and For Each statement work together. I normally don't use either, but it would be good so I could write less code where plausible. Here is the example I have:
Code: Dim Named As Variant Dim Ctl As Control Named = Array(Me.Namee, Me.ID, Me.Title)
[code]....
I am unsure how I include the array in this statement. I have tried replacing "Me.Controls" with the array name, but I get a 424 runtime error (no object). What I am trying to accomplish is for each item in the array I want to make it not visible if it has a null value.
I've been using a SELECT INTO statement to import data from a linked text file into a temporary table in Access. Something along the lines of :
SELECT [tblLink].[fld1] AS Field1, [tblLink].[fld2] AS Field2, [tblLink].[fld3] AS Field3 INTO [tblTemp] FROM [tblLink]
(There's an INNER JOIN in there and some Nz / CLng functions but just want to keep it simple...)
Now - I've just realised I also need to create a couple of extra 'dummy' fields in my temporary table (for later on in the show) and I need them to be Yes/No format (will set them to False at first, then run some separate queries later to update them)
I tried this :
SELECT [tblLink].[fld1] AS Field1, [tblLink].[fld2] AS Field2, [tblLink].[fld3] AS Field3, False AS Field4, False AS Field5 INTO [tblTemp] FROM [tblLink]
But this sets Field4 and Field5 as Number fields, with each record given a value of 0. What syntax is required in the SQL to make these fields Yes/No rather than Number?
I'm trying to make a nested if then statement in a query field, and I can't figure out why I can't get my formula to work:
Volume: IIf([MethodCode]="K",[total]*12.54*0.026873,IIf([MethodCode]="S",([length]*[width]*[depth])/2,IIf([MethodCode]="M" And [Location]="SH",[total]*5.08*0.026873,IIf([MethodCode]="M" And [Location]="C",[total]*18.58*0.026873," "))))
I keep getting the "data type mismatch in criteria expression" error. If I separate out all the individual if then statements individually, they work. But if I connect them all as a nested if then it doesn't work.
I basically want an SQL statement that will search all fields of a table for a certain value. At first I thought that tihs might work.. Code:SELECT * FROM tblExample WHERE *='blah';
But it doesnt :-/
So, is there any other method of doing this? I know that I could just include each individual field name, but Im lazy - and its a big table (plus I want to use a similar query on a few other tables, so I want it to be fairly generic).
this DB should transfer records from table to another table and vice versa, somehow i cannot make the other command button works. it took me a week to figure it out what is wrong with the code.
pls help me with my DB.
Somebody out there!
ill attached the DB if someone replies my request.
Why doesent this work aa = Me.Due aa = DateAdd("m", aa, Date)
Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < " & aa & " ORDER BY [Tasks].Staff_Name;"
Ive been trying to make a form that has a report list for filtering. Everything I research and do just isnt working for me. Ive followed a video about it and visited these sites.
Ive been trying to make a form that has a report list for filtering. Everything I research and do just isnt working for me. Ive followed a video about it and visited these sites.
Hi, all! I need some help. I am trying to figure out how to enable the subdatasheet in my subform. I've already told the root query to include the subdatasheet, and it works. My subform only allows datasheet view, and I've enabled the subdatasheet visible property in the subform. What am I missing? I really need it to show up. It would be SO cool if it would work. Thanks!
The user currently manual checks the Use Part? box but would I would like to automate with an update query. I have this query that needs to be updatable but has a group by and min to get the correct results. I plan to run this query by another update query to update the table, but its currently not updatable. Is there a workaround or does anyone have another scheme to go at it. Here is my current queries.
SELECT [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], Min([t_BOM Part Cross].[Alt Unit 1]) AS [MinOfAlt Unit 1], [t_BOM Part Cross].[Use Part?] FROM [t_BOM Part Cross] GROUP BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Use Part?] HAVING ((([t_BOM Part Cross].[Quote #])=[FORMS]![Parameter]![Quote #]) AND ((Min([t_BOM Part Cross].[Alt Unit 1]))>0)) ORDER BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[Quote #];
I have another query that I used a SUB SELECT ON THE [Alt Unit 1] but does not group the other fields to get the results but it is updatable.
SELECT [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Alt Unit 1], [t_BOM Part Cross].[Use Part?] FROM [t_BOM Part Cross] WHERE ((([t_BOM Part Cross].[Quote #])=[FORMS]![Parameter]![Quote #]) AND (([t_BOM Part Cross].[Alt Unit 1])>(SELECT MIN([Alt Unit 1])>0 FROM [t_BOM Part Cross]))) ORDER BY [t_BOM Part Cross].[Quote #], [t_BOM Part Cross].[Line #];
How do I make the windows default navigation work to the following:-Next record that navigates to next, but does not create a new record when at EOF. New record that creates a new record.
I've tried turning off 'Allow additions' for the form, but that turns off the 'New Record' button, so that's not right Perhaps it's me but the default way in which the navigation buttons work is strange. I would have thought the next button would only go next and wouldn't do new.
I want to write a code for a calculated text field in a datasheet. Right now the calculated text field is just set to ="view runs" but I want it to say "0 runs" if there are no runs in that Lot (for every lot there could be 0 to many runs). Im not sure how to make my IIf statement... every run has a runID and LotID_FK which is the LotID foregin key which is where the LotID is assigned.
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a combobox [CBreason] that reads from a table to show me why someone is gaining access to the building.
One of the options is "Incident".
When incident is selected I want a textbox [TBIncidentNo] and a button[BIncidentDup] control I made to appear.
I thought that the code:
If Me.CBReason.Value = "Incident" Then Me.CBIncidentNo.Visible = True Me.BIncidentDup.Visible = True Else Me.CBIncidentNo.Visible = False Me.BIncidentDup.Visible = False End If
Placed in the after update action of the CBReason box would sufice but it doesnt work
I'm guessing the value is not recognised fro some reason but i cant work out why. There is no error message or issus, it makes the box dissapear when there is no entry but no change for selecting incident.
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
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 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.
