Topics Search

VBA excluding (Ignoring) Comma

VBA excluding (Ignoring) Comma
Views: 30
so here is the problem. 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. Below is the code I'm using, however I get an error because all of the names have a comma in them. How can I get VBA to ignore the comma?

DoCmd.FindRecord DLookup("CSRName", "CSR Stats", "CSRName =" & Forms![Statistical Score Form]!Combo293.Value & ""), , True, , True
Sponsored Links:

More topics

 

VBA excluding (Ignoring) Comma

Preview
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

Preview
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()
DoCmd.OpenReport "Reportname1"
DoCmd.OpenReport "Reportname2"
etc.
End Function.
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

Preview
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

Preview
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

Preview
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.
 

TransferText

Preview
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

Preview
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

Preview
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 . .

Preview
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

Preview
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