VBA excluding (Ignoring) Comma
VBA excluding (Ignoring) Comma
I currently have a form with a combo box. And several text boxes. The combo box is tied to a list of Names (Formatted "Lastname, Firstname")The Text boxes pull data from a table based off the name in the drop down box.
This works fine using the next record or search feature. I'm trying to write a macro so that once a name is selected in the combo box it automatically updates the text boxes.
however I get an error because all of the names have a comma in them. How can I get VBA to ignore thecomma?
DoCmd.FindRecord DLookup("CSRName", "CSR Stats", "CSRName =" & Forms![Statistical Score Form]!Combo293.Value & ""), , True, , True
Printing multiple reports excluding empty reports
Previous threads in this forum have extensively discussed many ways of printing subreports whilst ignoring empty subreports. Interestingly, I came across another thread few years ago describing multiple reports printing using standard module. The VBA script can be, for example:
Public Function OpenMyReports()
So we can call on this function using codes from a switchboard item.
However, if the report has no values, only labels within that report with no values are printed.
But is there a way of not printing empty reports while using this code?
Could it be that additional codes may be necessary somewhere else
TransferText ignoring first record
I have a pretty straight forward TransferText module that seems to be ignoring the first record in the text file. The record is formatted exactly the same as the other records, so I am perplexed.
Nest Iif using Instr function
I am trying to write a query that will search a field for a string until it discovers a comma. If there isn't a comma I want the field left as is. If there is a comma I want it to grab all strings before the comma and then take the string after the comma and flip the arrangement to another field.ie (flipname)
example if a field has [Smith, John] I want it displayed as John Smith
Here's is the code I was attempting to use below, it generates syntax errors!
SELECT Exercise1.name, Iif(Instr[name],",")=0,[Name], Mid([name],Instr([name]),+1,instr([name]),",")-1 as expr
VBA Parse Tab Delimited Text File
Data supplied in tab delimited text file. Unfortunately there are more than 255 columns in the text file and can't directly import into an MS Access table (max 255 columns). Now trying to parse the text file (using VBA) to remove unwanted columns - idea being that once below 255 columns I can load into table.
I'm okay with parsing on tab character, chr(9), but I've hit problem if data record contains comma character. VBA INPUT verb appears to consider comma to be a variable delimiter.
I need to move comma-delimited text files into Access.
I want to loop through a directory using VBA and create new tables in Access for each text file.
Right now, my VBA shell looks like this,
When I use Access's import wizard and step through (Delimited >> Delimiter = Comma, Qualifier = " >> Finish), my file imports without errors.
I save an import specification with those settings (Delimited, Delimiter = Comma, Qualifier = ") and re-import the file to verify the specification. Using the wizard, (Advanced, Specification = "Import" >> OK >> Finish). My file imports without errors.
But when I run the shell above, my file imports with 279 Type Conversion errors.
This occurs whether the syntax that follows TransferText is acImport or acImportDelim.
Query for excluding TOP N records
I need to design a query which select all records excluding the Top 5 based on totalmarks.
Example :- say I have 25 candidates and their total marks.
The candidates are divided in TWO locations.
I have to select all candidates except top 5 based on their total marks(for each location)
Can I generate a query that will only display the results of a table excluding the results of previous three queries? If yes, how?
Calculate DateDiff Excluding Weekends in SQL or UDF Run on UNTRUSTED Location
I am working on a small database and need to calculate differences between dates excluding weekends. I have found a large number of posts regarding doing this with VBA functions, but I need to do it strictly in SQL (it needs to feed directly into query results on a database whose location cannot be set as trusted due to my company's network requirements/regulations).
If anyone knows of a UDF that will perform this function without the database being stored in a trusted location, I could use that instead, but all of the UDF's I can find in the forum require a trusted location to run.
VBA Ignoring Stop . .
I am not sure what I did wrong. I have a stop on my vba code so I can step through it. The code is on the "current event" of a form. When I go to the form and run it, the code seems to work, but it does not stop at any of my break points. I think I may have accidentally changed some setting - but I am not sure which one. Can anyone advice
Query of comma separated data to return related value
I am trying to write a query that will look at a field of comma separated data (Table1; ItemCode) and return a value from another table (Table2; Order) related to the individual strings in the comma separated field. The screen shots below illustrate what I am trying to do. I am not sure where to start with this
Reselect Listbox entries from text string read
I have a gathering of items selected in a multi-select Listbox saved to a table as a comma seperated string. Can I use that string to re-select the listbox items, let's say, if the end user was re-opening to modify previous selections?
I have code to parse and read from comma to comma - what I need is how to reselect the list items
getting a comma separated string from a recordset in MS Access
I have an access query that has 2 columns: CAUSE and count(records) having the respective CAUSE value. I want to add another column that will give me all the ACCOUNT NUMBERS same as the number mentioned in the COUNT column. Also I want all these ACCOUNT NUMBERS to appear in comma separated format on the crystal report.
Any suggestion as to how we can convert the recordset to a comma separated string
Excluding Records in a Query
I have two tables; table one has all the values, table two has values I want to exclude. Would I create a query excluding by using "not like " and then do another to get the values that I want?
Mid, Left, Right Functions
The records in my table field contains [first name], [last name] together and I can't remember how to separate them without defining the number of characters. What I would is to read any alpha characters before the comma (for first name) and alpha characters after the comma (for last name).
field with musltiple entires
I have a column in a table where in one cell, I have multiple entries like: 2, 23, 45, 88 - separated by comma.
I was wondering what I may do in teh quesry, so that I get rid of the entries after teh first comma in the cell
Null values being incorrectly excluded
My query is excluding null values in error. Can someone assist me in where my code error is? I have one table I'm querying from and using two fields to filter. Here is the statement that is excluding the two discharge disposition types, but is also excluding all null values:
Or (tbl_finaloutput.[Discharge Disposition])<>"PVH Inpt/Outpt")
AND ((tbl_finaloutput.[Patient Type])="er"
I want to make sure I return all Null values in the Discharge Disposition field. For example, there may be another record where the patient type is "er" but the discharge disposition field is blank--this should be in the result because it's an "er" record. I know that I need to define that null values should be recognized as valid, but not sure how
Separating VALUES in FIELDS/COLUMNS
I have a table with two columns, one column is Address and the second column is City. However, the values under those fields are combined with Address and City, separated by a comma. How do I delete everything before the comma in the City field and delete everything after the comma (including the comma) in the Address Field
currently it looks like this:
1280 One St, San Jose 1280 One St,San Jose, CA<----Values
Grabbing a string right of a comma.
I've found a lot of stuff for getting what's on the left side of a comma, but I'm trying to grab what's on the right side. I found the following for getting the left side: Left(Me.Employee, InStr(Me.Employee, ",") - 1)
Can I do the same with Right? Using -1 (and other numbers) I still kept getting letters to the left or not all of the letters to the right of the comma.
I also saw something called Split, which I'm looking into
If statement parsing in a query
can an if statement be used to parse/split a name in SQL? I'm assuming it would be quicker than VBA. Here's my current code:
' My major worry is that this could be a lot quicker if done in SQL, but that is very complex to design
Do Until .EOF
' This block splits the advisor's name
wholename = rsTempTable!agentname
'if there is a comma in the name
If InStr(wholename, ",") <> 0 Then
rsTempTable!AdvisorLastName = Left(wholename, InStr(wholename, ",") - 1)
rsTempTable!agentname = Right(wholename, Len(wholename) - InStr(wholename, ",") - 1)
Else ' There is no comma, presume a company name, stored in AdvisorAdvisorLastName field
rsTempTable!AdvisorLastName = rsTempTable!agentname
rsTempTable!agentname = ""
Also, can I pass in a variable to an update query? Such as a date I retrieved earlier and stored in a VBA variable
Create comma delimited string
I would like help creating a macro to create a comma delimited string from a column in a query. The string can be in a word doc or .txt file I just have to be able to copy the string and paste it in another application. I am pretty new at access and hope I'm describing it correctly.