Ex.
Code:
Kill "C:\Temp\Test.xls"
ajalwaysus
Last Activity: Jan 18 '19, 06:16 PM
Joined: Jul 16 '09
Location: KY
-
You could try the Kill function to delete the file before re-creating it.
Ex.
-AJ -
Ok I'm sorry but I have to ask...Why?!
-AJLeave a comment:
-
Well boy do I love SQL! =PLeave a comment:
-
You cannot pass a null to a date, so you may need to use an if statment to build your query.
Example:
...Code:If IsNull(BirthDate) then strSQL = "SELECT *" & _ " FROM Table1" & _ " WHERE Person = 'AJ'" Else strSQL = "SELECT *" & _ " FROM Table1" & _ " WHERE Person = 'AJ'"Leave a comment:
-
ADzii's solution should work, but since I don't trust "TOP" to give me a specific record, I would use a query like this:
Also if you use ADzii's code, I cannot stress enough how important the ORDER BY is, you cannot make this work without it.Code:SELECT MAX(Table1.TheDate) AS MAX_DATE FROM Table1 WHERE [TheDate] <= Date();
Good Luck,
-AJLeave a comment:
-
There appears to be a "." dot in your code that shouldn't be there i believe, and there needs to be a space.
Should be:Code:strSQL = "INSERT INTO" & Me!MyShow & _ "SELECT *. FROM [InfoTable];"
Maybe that is it.Code:strSQL = "INSERT INTO" & Me!MyShow & _ " SELECT * FROM [InfoTable];"
-AJLeave a comment:
-
Try:
and if this doesn't work, please post qryCustomer #3 code.Code:SELECT [qrycustomer #3].Facility , 1 AS [Order], Format([MonthYear],"mmmm") AS [Month] , DatePart("yyyy",[MonthYear]) AS [Year] , "Compliant Observations" AS Items , CStr([qryCustomer #3].TotalCompliant) AS Result INTO tmaCustomer FROM [qryCustomer #3];
-AJLeave a comment:
-
Glad to help, and welcome to Bytes.
-AJLeave a comment:
-
Your problem is that you are assigning the value to the query, the you are incrementing it, but you don't reassign it.
This is what it should look like to do what you want.
...Code:Sub SetLEAs() Dim MyCnt As Integer Dim i As Integer For MyCnt = 1 To 5 Application.SetOption "Confirm Action Queries", 0 MYSQL2 = "INSERT INTO DGBTestVariations2 "Leave a comment:
-
Try This,
To save space and time, VBA usually won't scan your entire recordset unless you tell it to. (i.e. MoveLast) So as you had it, VBA only sits...Code:Dim dbs As Database Dim rs1 As Recordset, qdf1 As QueryDef Set dbs = CurrentDb Set qdf1 = dbs.QueryDefs("SubQuery") Set rs1 = qdf1.OpenRecordset(dbOpenSnapshot) rs1.MoveLast rs1.MoveFirst rs1.recordcountLeave a comment:
-
If each user has a separate front-end then yes the global variable only stores each users IDs distinctly, but if they are both using the same front-end db then I have no idea. I have never attempted this with a single front-end, best practices recommend a separate front-end for each user with a single back-end.
-AJLeave a comment:
-
trixxnixon,
I do not have any of the knowledge you seek, but I do have some advice. This is a help forum, so it may help your case to get any responses by actually posting your intentions. i.e. What do you wish to achieve by getting takers to your thread?
-AJLeave a comment:
-
orCode:Me.Combo31.RowSourceType = "Value list" Me.Combo31.RowSource = "spvr;mngr;user"
This second one is untested, but the first one should work.Code:Me.Combo31.RowSourceType = "Value list" Me.Combo31.RowSource = "'spvr';'mngr';'user'"
Let me know if this works,
-AJLeave a comment:
-
Please post your code, so we can see what you are doing.
-AJLeave a comment:
-
Ok, like Linq said, we still don't know what you are doing. But I can tell you what to do to get the results you are looking for, what you need to do:
1. Assume you have a text box named txtYear
2. Create another text box and go to its properties and put "=DaysInYear([txtYear])" in the control source field. (no double quotes!)
3. Keep this code with the tweak I put in:
...Code:Public Function DaysInYear (ByVal Year As
Leave a comment:
-
Where have you set DaysInYear([Text_year]) in your text box. Did you put it under Control Source?
Also, whether or not this is how you do it now, try to make sure it appears as =DaysInYear([Text_year]) in the control source.
-AJLeave a comment:
-
Are you allowing them to input the year in one field and showing the number of days in another, or are you trying to change the value in the same text box that they just entered the year in?
This may be your problem, if that is the case. I don't think you can overwrite a value while that value is being used to calculate another value.
I could be way off, but let us know.
-AJLeave a comment:
-
I am getting off work, but if no one responds before I do, I will try and respond in the next hour or so. Sorry.
-AJLeave a comment:
-
Ok what does this mean, are you going to be the one who imports the data and then run the necessary code to get the Table3 data? Or are you going to be handing this off to someone else after you import this data. I don't think I have a problem understanding the data as you have put it, but you need to explain better the flow of what you intend on doing.
Also, if you have already created Table 1, Table 2, and Table 3, then you should...Leave a comment:
-
This is just a query, you could store it as such, or you could put it in VBA, your choice. Maybe you should state how you wish to use this code, as well as if the data you have is stored in Access tables, and we can help you figure out the best place to put this query.
-AJ...Leave a comment:
No activity results to display
Leave a comment: