I am trying to build a new sql statement for a query using a string from items selected in a list box on a form. I have the string in place and keep getting syntax errors for the new sql statement. The code is below.
strSQL = " SELECT JG_tbl_LMEMP.DEPT_CODE" & _
" FROM JG_tbl_LMEMP" & _
" Group by JG_tbl_LMEMP.DEPT_CODE" & _
" Where JG_tbl_LMEMP.DEPT_Code IN(" & strCriteria & ")"
qdf.SQL = strSQL
I am trying to write vba code to enter a form. A am using an SQL statement in vba to enter the form with it.
I want to include a string variable in the WHERE clause of the SQL statement. The string variable is introduced in the DIM statement before coming up to the SQL statement.
The following is the SQL statement. numrukarta is the string variable I want to introduce. I need to know the exact punctuation to be able to insert it into this statement.
StrSq1 = " SELECT Employees.NName, Employees.SSurname, WorkItems.IDcardNo, WorkItems.DDate, WorkItems.EntryTime, WorkItems.FinishTime, WorkItems.Roster" _
& " FROM Employees INNER JOIN WorkItems ON Employees.IDcardNo = WorkItems.IDcardNo" _
& " WHERE (((WorkItems.IDcardNo)= numrukarta) AND ((WorkItems.DDate)=Date()) AND ((WorkItems.FinishTime) Is Null);" AND (Not (WorkItems.Roster) Is Null))
From a dropdown field in the form it's currently possible to choose a geographical region for which to generate a report. The data populating this dropdown is pulled in from a Value List as follows:
" ";"*";1;2;3;4;5;6;7;8;9;10;11;12;13;EU;WD
I now need the ability to choose various different regions simultaneously which is not possible with the current method. I've looked into a nested continuous form and a multi-select combo box or list box, but none of these are as user friendly as my preferred method.
What I would like is 15 checkboxes plus a 16th to select/unselect all. When any of these checkboxes is checked, I need to create something like a dynamic value string or temporary table to hold the list of chosen regions until the generate button is clicked at which point the data is used to generate the report and cleared. I also need a piece of code to check/uncheck all the boxes.
Hi all, i'm currently working a MDB project that aims to develop a front end access solution for users of the sql database. The system is used only to made adjustments to the WHERE clause of the SQL pass-through statement. The SELECT and FROM statements are pre-determined and users won't need to update this. The result is a read only. You might be wondering why i don't use ADP instead, well one of my limitations is that i won't have write access to the database. They are afraid i will corrupt data...
I want to use a series of text and combo boxes to build my WHERE statement, which will be added to the main SQL statement and then pass-through to the server, making use of its much beta processing capabilities. However as i'm trying to read up on VBA, i still have very little knowledge on how this can be done. Its actually similar to the Filter By Form option in datasheet view when i open a pass-through query. However, conditions applied through here means the processing is done on the user's computer, through testing i found this to be unreliable as it causes access to hang frequently. I would love to have this filter by form view available up front to the user before he even executes the query. As i mentioned, he keys in the criteria/conditions and access builds an sql where statement and appends it to the main SQL statement.
any idea how to do this? how do i capture user input and make access construct a WHERE statement from it For example
textbox daterange
Enter range of dates in here: date 1 and date 2
the where statement would then be "WHERE date BETWEEN date1 AND date2
Or is there other more efficient alternatives? sorry i'm very new to VBA and form development, would appreciate lots of advice and answers.
Many Thanks
Eric
How can I incorporate outlook and the use of contacts in outlook into my access database?
View 2 Replies View RelatedMS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
I am relatively new to VBA and have this loop (see below) was wondering if it was possible to build it into a variable, so I could call upon it within an IF statement.
Set ctl = Me.listSeeAllAssets
For Each varItem In ctl.ItemsSelected
rs1.AddNew
rs1!AssetNo = ctl.ItemData(varItem)
rs1!DateOfService = Me.DateOfService
[Code] .....
I imported an extensive table from Excel. Subsequently, changes were made to the data in the Excel spreadsheet and I would like to incorporate these changes into my Access table. I did not link the two and I do not want to import the Excel spreadsheet again because I have made several changes to the Access table after importing it. Is there any way to have Access go through the information in Excel and make the modifications in the field that suffered the changes?
View 3 Replies View RelatedI am trying to build an event when I double click a field. As soon as I click on Code Builder, access crashes. It does this with every access database I open.
View 14 Replies View RelatedI'd like to override the default behaviour for deleting records in a form.Specifically, I want to build my own custom delete procedure so that when the user presses the Del button, my code fires to complete the deletion of the selected record(s). In order to do that, I'd set Allow Deletions = No for that form. I'd also want to code the KeyDown event for the Del key so that if record(s) are selected, my custom delete code fires, else the default behavior for the Del key happens.I'm primarily interested in how I might code the KeyDown event.
View 12 Replies View RelatedI'm making an accounts package.
I've made a graph of revenue by customer but due to the number of customers it's a bit hard to see the customer names.
So I would like to be able to select my own custom group of 5 customers to plot on the graph.
To do this I think I need a query that selects the 5 customers which is populated from 5 comboboxes on a new form that the user can select customer names from but I'm not sure of the VBA that links all this together.
Anybody see anything wrong the syntax for the below query. I'm trying set up this query
to pass a string to 'struser' for multiple users. I'm getting 'Syntax error in FROM
clause'. Thanks ! ! !
Dim strSQL As String
Dim struser As String
struser = "MIKE"
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryUser")
strSQL = "SELECT Salable_Figures.Loan_Originator_Code," & _
"Salable_Figures.Loan_Originator_Name," & _
"Salable_Figures.Lastname_Sort," & _
"Salable_Figures.Team, Salable_Figures.Plan," & _
"Salable_Figures.Monthly_SCountOfLoan_Program_Code AS Monthly_SCount, " & _
"Salable_Figures.Monthly_SSumOfNote_Amount," & _
"Salable_Figures.Salable_Monthly_Goal," & _
"Salable_Figures.YTD_SCountOfLoan_Program_Code AS Yearly_SCount," & _
"Salable_Figures.YTD_SSumOfNote_Amount AS Yearly_SSumOfNote_Amount, " & _
"Salable_Figures.YTD_SSumOfNote_Amount AS Yearly_SSumOfNote_Amount," & _
"Salable_Figures.Yearly_Salable AS Yearly_Salable_Goal " & _
"FROM Salable_YTD_Figures INNER JOIN Salable_Figures" & _
"ON Salable_YTD_Figures.Loan_Originator_Code = Salable_Figures.Loan_Originator_Code " & _
"WHERE Salable_Figures.Loan_Originator_Code)=" & struser & _
"';'"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "qryUser"
I have a 3rd party database where I have no control over how the data is entered. I've been given the task of creating a Crystal report that would need to gather data from two databases. The link between the two databases in my report would be on a quote number. In one database, the quote number is in it's own field. In the 3rd party database, the quote number is stored in a memo field along with other data.
Here's an example:
4000 C7875
9003267 T7761
90000167/4010/T6895
4010 T7152A
TPCA #1756/2914
The data I'm after is
C7875
T7761
T6895
T7152A
*No data from the 5th row since the data after the "T" isn't numerical
So far I'm thinking of using an IIF statement to check for the existance of a C or T, then if true, use a nested IIF statement within the first to check for numeric, if true, use the Mid function to pull out the quote number.
My first concern is this could become a complicated IIF statement and was wondering if there was another direction I should be looking in acheiving this.
My second concern is if I go with this method, some of the quote numbers have a space after them, some of them have no space after them, some may even have a "/" after them. How would I go about accomplishing this?
In my statment below the ID is an autonumber. I placed it in the first part ofthe SQL string..but not where I have to variables referenced...
I have to add it to the SQL statement...Its not a varible although I can make it one by getting the largest number nad adding one...
Do I have to make it a non-autonumber and do it like I said above
Or is there another way to treat the autonumber (ID) in the SQL INSERT INTO statement...
THanks
Code:MySQL = "INSERT INTO dbo_data ("MySQL = MySQL & "ID,Name,Owners_Residence,Notice_Expiration,Notes,N otice_Number,Status,Notice_Date,Property_Address,N uisance_Type,Addition_Name,Range,Lot,Block,Zip_Cod e,Display_PID,Sec_tion,Township,Property_City,Prop erty_State,Property_Zip"MySQL = MySQL & ") values ("MySQL = MySQL & "'" & varName & "','" & varOwners_Residence & "','" & varNotice_Expiration & "','" & varNotes & "','" & varNotice_Number & "','" & varStatus & "','" & varNotice_Date & "','" & varProperty_Address & "','" & varNuisance_Type & "','" & varAddition_Name & "','" & varRange & "','" & varLot & "','" & varBlock & "','" & varProperty_Zip & "','" & varDisplay_PID & "','" & varSec_tion & "','" & varTownship & "','" & varProperty_City & "','" & varProperty_State & "','" & varZip_Code & "'"MySQL = MySQL & ");"
In the following statement i tried to send BeginningDate(textbox value) and
EndingDate(txtbox value) to the SQL stmnt but it gave an error saying "Type
Mismatch" even though i used CDate method. answer for this problem is greatly
appreciated......
"WHERE Project.tProjTitle='" + Trim(cboProjectTitle) + "' AND
Project.nProjId=Indicator.nProjId AND Indicator.tIndicatorName='" +
cboIndicator + "' AND Indicator.nIndicatorId=IndicatorData.nIndicatorId AND
Region.nRegionId=IndicatorData.nRegionId AND
Institute.nInstituteId=IndicatorData.nInstituteId AND" + _
"IndicatorData.indicDate Between '" + CDate(BeginningDate) +
"' AND '" + CDate(EndingDate) + "' " + _"GROUP BY
IndicatorData.nRegionId, " + Trim(Replace(cboRows, "_", ".")) + " "
I'm creating a string with an IIf statement and placing a comma between values. How do I delete the last comma at the end of the resultant string?
View 2 Replies View RelatedI have an endpoint for students so they can report any faults in the compound.I want to build a form that contains buttons of a certain value so that a student clicks on the button.Is fed directly to the table and pass to the next form to another value table.
View 7 Replies View RelatedI am developing a calender to display HOTEL room occupancy (past,present) and combine with future "outlook" dates and 1/0 values from active registrations that go beyond the present date.
I am working with MS ACCESS 2007. My problem is ONE SPECIFIC QUERY AND IIF STATEMENT. I want to concatenate some text (using &) along with numbers converted to text (using the CSTr function). I am using iif function and I want to use the full text string as a variable argument to be executed in the iif function. The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.
But, I do not want the final result as the argument. I want the query and iif to use the string expression written into the argument as the variable argument that can be calculated based on OTHER numbers that change everyday in the daily run of the calender.
The field in the statement [calc number] is a date conversion factor that changes everyday.
I want the iif statement to execute using the string as a variable argument. I am writing only within the QUERY to define the query object. I am not writing into any SQL module or code. My field definition and iif statement is below (calc number changes everyday)
CalDate18: iif(18 greater date();"[p" & [calc number] & "]";0
I do not have greater-than key on this international keyboard !
When I use this in an update query, I get format conversion error. When I use the same definition in MAKE-TABLE query, it gives the resolved value "[p1]" for the value of [CalDate18] ......... that is not what I want.
I want the string to be taken literally and executed. Seems there may be a special character to precede the argument or WRAP the argument such as done in Excel. Example # "[p" & [calc number] & "]" #
I'm trying to get a value from a spreadsheet to import into my MS Access database. Currently I am trimming the spaces/carriage returns from it but need to strip some more data from the value.
Here is my code.
trimmed_department = Trim(Replace(new_department, vbCrLf, ""))
Example value being "123 Point 5 Finance and Accounting"
I want to use Mid (I think?) to remove the "123 Point 5" (it is always the same with no exceptions) but don't know how to use it as I don't know how to use multiple parameters within a string.
I want to add a string as year to a date.
Somehow it doesn't work out. It should extract all records with Valid_from = 01.01.2013 and valid_to = 31.12.2013. The Year assignment works.
Code:
Public Sub BEN()
Dim strSQL As String
Dim t As Date, s As Date
DoCmd.SetWarnings False
Year = Right(pricedate, 4)
t = 1 / 1 / " & Year & "
s = 12 / 31 / " & Year & "
strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _
" FROM TRANSFER_PRICES " & _
" Where [Valid_from] = " & Format(t, "#mm/dd/yyyy#") & _
" AND [Valid_to] = " & Format(s, "#mm/dd/yyyy#")
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
I have set calendar control 12.0 up and everything works how I want it to (click date and peoples names in a table to the left to show scheduled meetings on that day). What I want is to add a string on top of the calendar. For example, this monday I would like for it to say "Memorial Day" on the physical calendar itself.
View 3 Replies View RelatedI would like to cut off the first 6 and last 2 Charaters in an after update event but not sure how, I cannot use mid as the length of the string may change but never the first 6 or last 2, can some one show me how it's done ...
View 4 Replies View RelatedI have made a system for managing service calls and fieldworks.Part of this is checking the boards we install/service are working correctly.When the engineer calls in, we check the board - enter serial number, via ODBC talks to main server DB and pulls back what is listed below, along with ID and date/time which is all displayed in a list box.
9853911264,W-AMR,3,2:320:0:52,MAIN STORE,3.57,0,18,001.004.041,0,0*75
This works fine. In the string above are certain bits of information that need to be checked to ensure they are accurate and the board has been programmed correctly. What I want to do it, is when the user single clicks on the list box, it parses out the various sections of the above string and enters them into some text fields on the form. With some code these can then be checked to see if they are correct or not and alert the user if they are or not.
The checking part I can do, the part I am struggling with is parsing out the correct parts from the string.The parts will be the same parts required each time, and the string parts wont vary, just not sure how to go about it.
I am trying to set up some template emails using text someone has entered in a form with a variable indicated with a key word in brackets aka. [ChangeID] or [ChangeDate]. The field on the form is formatted as Rich Text so I am getting http code. (No problems yet) In the form the template is required, I lookup the template required and I get the string. I replace the brackets with the following
Code:
strleftB = Chr(34) & " & me."
strRightB = " & " & Chr(34)
strTemplate = Replace(strTemplate, "[", strleftB)
I then get a string but in need to convert part of the string into variables, before I capture the correct output for my email
Code:
"<div>RFC Submission: <strong>" & me.ChangeID & ", </strong> " & me.Details & "</div>"
My question is: what is the best way to split the string into strings and variables
I am trying to use a textbox value to call vba code and can not get it to work.
I keep getting an error on the call str1 line.
Code:
Sub formscript()
Dim str1 As String
str1 = [Forms]![fscripts2]![t3]
'MsgBox str1
If str1 = "" Then
str1 = "err1"
Exit Sub
Else
Call str1 ' this is where it call the script based on value in textbox
End If
End Sub
There are so many threads on here for emailing a PDF version of a report. I've put together a nearly complete bit of VBA that will do what I want - except for one thing.
Basically I have a personnel table with an email field. I Also have a field that details if a person is available or not ("Home" means they are available)
I need to create a string that is comprised of all the email addresses of the personnel who are "Home". In another thread I read that this can be done using a SQL statement.
So this is the VBA that I have so far - I commented the SQL for now because I don't quite know how to use it here.
Also, I have created the string, called "emailsList"
Code:
Private Sub Email_Button_Click()
'SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)="Home"));
Dim emailsList As String
DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report.", True
End Sub
Do I need to do a loop that concatenates?