Modules & VBA :: Finding Number In String - If Found Function Is True

Apr 17, 2015

Have a strings like this

Code:
dsa;hwq;67;dk;71c

Code:
uqiea;762c;iyh

Is there any possibilites to write a function which find number in string, even if some part (between ";" and ";") has number and text (like 762c)?

if there is number in string then function is true

View Replies


ADVERTISEMENT

Modules & VBA :: Function - Can A String Be Optional Variable?

Aug 26, 2014

I am amending some Code I found online for an audit table, I need to store additional information in the table that is associated with some forms but not others.

I have researched about putting optional variables in, but I read this only works with the type VARIANT.

Is there a way to make a string optional as my fields contain text?

here is my code so far:

Sub AuditChanges(IDField As String, UserAction As String, Optional UserID As String, Optional DeviceID As String, Optional SimID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date

[Code]...

View 5 Replies View Related

Modules & VBA :: Edit A String Or Use Wildcard In Search Function

Jul 18, 2013

Access 2007

I can't figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.

Ultimately what I'm trying to do is - when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the "." with enough zeros for 10 digits.

Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.

Function Search()
Dim lssql As String
Dim lsSn As Recordset
Dim db As Database
Dim lsMessage As String
Dim sMsg As String
Dim vRetVal As Variant
Set db = CurrentDb()

[Code] .....

View 2 Replies View Related

Modules & VBA :: SQL CAST Function - Return Integer Value Of String

Aug 28, 2013

Need to use CAST to return integer value of string (digits as data type string).

Where clause looks like this:

... Where Cast([Price File] as int) > 0

works fine in SQL Server but not sure what syntax is in VBA . Using Paul Baldy's suggestion to set Select statement as string and do the debug.print to verify that SQL has no goofs ... looks good but not to Access. What is proper syntax?

View 12 Replies View Related

Modules & VBA :: Fixed Path Information - Function To Return String

Jun 20, 2014

The code has fixed path information on a lot of places in different SQLs (DoCmd.RunSqL command). I want to replace fixed path info with variable path info. Variable path info is stored in the table.

I managed to achieve that in the following manner:

Code:
Dim db As Database
Dim dbName as String
Set db = CurrentDb
Set rs = db.OpenRecordset ("TableName", dbOpendynaset)
rs.FindFirst ("ID = " & 2)

[Code] ....

where I would use as variable Function name instead of dbName.

How to make module that will enable to use Function name as variable path information for SQL queries?

View 10 Replies View Related

Modules & VBA :: Using Public Function To Feed A Variable String To Query

Feb 26, 2014

I am using a public function to feed a variable string to a query. So far I have got:

Code:
Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String
Dim cslStr1 As String, newStreet As String
newStreet = Right(streetVar, Len(streetVar) - Len(newFL))

[code]....

However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. Why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?

View 7 Replies View Related

Modules & VBA :: How To Find If Field Contains Number In The String

Oct 7, 2014

I am looking to find if a field contains a number and then build a case statement depending on which number is found. The field will contain data just like this:

Quote:

Repaired frequency response and grounding issues. Replaced 2 Hybrids, capacitors, and connectors. Tested MER/BER and operation to specs.

Here is my code that did not work:

Code:
If Me.txt_work_comm1 Like "*Hybrid" Then
'Sets up auto priced based on number of hybrids entered
'1 hybrid
If Me.txt_work_comm1 Like "*1" Then
strCriteria = "repair_item = 'Charter RF Amplifier Repair + 1 Hybrid' AND profile_types = 'Alpha'"

[Code]....

To Summarize:
1. I need to find if the word "Hybrid" or "Hybrids" is in the field
2. Then I need to know how many to determine a price

View 1 Replies View Related

Modules & VBA :: Unable To Convert String Into Double Number Format

Jan 31, 2014

I have been trying to convert string into double number format. I am running a SQL query in VBA that returns a double number format; however my understanding with SQL queries in VBA is that they return string only. The results are showing up perfectly fine when I run the query in the query editor; however when I try using the returned value in further calculations in VBA I keep getting a "Type Mismatch" error.

View 7 Replies View Related

Modules & VBA :: Counting Number Of Occurrences Of Line Feed In Long String

May 1, 2015

I have to count the number of occurrences of Line feed (vbLf) in a long string. The code I am using is:

MAX = TLine.Split(vbLf).Length - 1

It does not work.

View 5 Replies View Related

String Found In *deleted* Query/form

Aug 20, 2007

I've created a function to search for a string in SQL definitions:
Public Sub SearchInQueryDefs(strSearch As String)

Dim qdf As QueryDef
Dim qdfs As QueryDefs
Dim blnFound As Boolean

Set qdfs = CurrentDb.QueryDefs

For Each qdf In qdfs
blnFound = InStr(1, qdf.SQL, strSearch) > 0
If blnFound Then
Debug.Print "Searching : " & qdf.Name & "...";
Debug.Print " - found"
If vbNo = MsgBox("Found!" & vbCrLf & vbCrLf & "" & strSearch & " found in "

& qdf.Name & vbCrLf & vbCrLf & qdf.SQL & "" & vbCrLf & vbCrLf & "Click 'Yeah' to

continue search, 'Duh' to stop", vbExclamation + vbYesNo, "SearchInQueryDefs") Then
Exit Sub
End If
End If
Next qdf

MsgBox "Done searching.", vbInformation, GetAppTitle()
End Sub

Using the following statement (in the immediate window) i get the following result:
SearchInQueryDefs "Queries"
Searching : ~sq_cfrmReports~sq_clstQueries... - found
However query "~sq_cfrmReports~sq_clstQueries" doesn't exist.
It is probably a query which populates the listbox "lstQueries" in the "frmReports"

form. But that form doesn't exist in my database. I have deleted it some time ago. I

thought that Compact and Repair got rid of stuff like this.
I found the definition in the MsysObjects and with this Id also in the MsysQueries.
So my question is obvious: what is this, why is this and what can i do about it?

Thx!

View 6 Replies View Related

Number Of Records Found

Mar 23, 2006

A form i have gains its records from a query. I know that the number of records found is displayed down the bottom, but is it possible to have a text box displaying this, so i can choose where on the form i have it?

TIA
Dusp

View 1 Replies View Related

Displaying Message If ID Number Is Not Found

Apr 11, 2005

Dear All:

I am using a combobox to search for students by their STUDENT_ID.

I wish to display a message "STUDENT NOT IN DATABASE" if the ID number is not in the database when it is typed in.

Here is the code I am using:

Private Sub Combo801_AfterUpdate()
' Find the record that matches the control.
Dim rs as object

Set rs = Me.Recordset.Clone
rs.FindFirst "[STUDENT_ID] = & Str(Nz(Me![Combo801], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any ideas where to place this message?

Many thanks,

Dion

View 2 Replies View Related

Modules & VBA :: Convert Number Column Into String Column

Feb 28, 2014

I want to convert a number column into a text column.

Number Column is called Customer.

Below I can't match to the other table containing the text column Customer No.

How can I convert columns in vba?

Code:
strSQL = "SELECT CALCULATED_Forecast_TNS.*, USEFUL.TRP_in_EUR " & _
" FROM USEFUL INNER JOIN CALCULATED_Forecast_TNS ON (CALCULATED_Forecast_TNS.[Reference No] = USEFUL.[Reference No]) AND (USEFUL.[Customer] = CALCULATED_Forecast_TNS.[Customer NO])"

Set qdf = CurrentDb.CreateQueryDef("REPLACABLES", strSQL)

DoCmd.RunSQL "UPDATE REPLACABLES SET CALCULATED_Forecast_TNS.TRP_in_EUR = USEFUL.TRP_in_EUR"

View 1 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

Finding Field With Biggest Number Of Characters

Feb 26, 2008

I have a table with about 20.000 records.
From this table I would like to "search" in a column the field that holds the text with the most characters.

Is there a way to find this with a formula or expression?

View 3 Replies View Related

Queries :: Finding Latest Number Using A Query

Jun 19, 2013

I need to find the latest number using a query.

All of the numbers follow this format:

[Doc No]
ABCD123

but the tricky part is the number could be suffixed with a revision. It is the latest revision I would like to find. For example, If I have a number followed by 3 revisions I would have:

[Doc No]
ABCD123
ABCD123+200
ABCD123+300
ABCD123+400

I need a query to give me the latest revision, in this case

[Doc No]
ABCD123+400

There is a possibility there are no revisions in which case the latest will be

[Doc No]
ABCD123.

How is this possible in a query???

View 5 Replies View Related

Get String Function

Apr 1, 2008

Hye

I have an Access database in which one of the queries has following fields:

Reference1:Getstring([Reference],1) and the second is

Reference2:Getstring([Reference],2)

I cant find any reference to Getstring function's use or functionality. Microsoft's website only says "Obsolete" indicating this is no longer used. (i am using access 2003)

Any help is appreeciated.

Many thanks
Jay

View 3 Replies View Related

DatePart Function, String Functions

Nov 1, 2005

Hey all I have a I was wondering if you knew how I can get a part of my datepart function out, I have taken the year from the the date with the date part function and now I need only the 05, so what can I do to get it out? Thanks MY CODE: Dim Num as string Num = DatePart("yyyy", STRDATE)
I looked up the code for a right function and it is suppose to work on a string, so I tryed it on this. My CODE:
Dim Year As String
Year = RIGHT(Num, 2)
I obtained a type mismatch WHY???

View 7 Replies View Related

Function For Combining 2 String Expressions?

Jan 12, 2007

I am having a conversion problem combing the following 2 string expressions.

Condition 1: Format([TestDate],'mmm dd", "yyyy')

Condition 2: NZ([TestDate],"No Exam Found")

I want to convert the dates to string values, and evaluate for Null fields, if Null display "No Exam Found."

I am having trouble getting both of these string expressions to work together.
If I use them together the NZ statement fails.

I can get the date value in the column to display as a string "Feb 14, 2004"
or
If I use the 2nd condition if a field is Null display "No Exam Found", separately.

But how to do I get these two conditions to work together on the same column of information?


[TestDate]
02/14/2004 ---->"Feb 14, 2004"
03/22/2005
07/21/2005
05/11/2004
10/12/2004
(BLANK) ----> "No Exam Found"
09/28/2004
03/14/2006


I am comfortable with Access, and basic Queries, but I am a non-programmer. I am tipping my toe in the Access programming water with Michael Alexander's book "Microsoft Access Data Analysis: Unleashing the Analytical Power..." and his Web tutorials... which has given me enough confidence to tackle this.

I have searched the web and have been toiling over this for about 2 weeks now.

Thanks

View 5 Replies View Related

Queries :: How To Use String Split Function

May 26, 2013

How many kind of string inbuilt function we have or if we create our on function so how to call that function in your query...

View 6 Replies View Related

Modules & VBA :: Sum Up 10 Columns For Same Row Of True / False

Sep 19, 2014

There are around 100,000 records to update. Would a SQL Statement be more efficient?

It is a local table being used to sum up the results of a handful of rules.
The columns can only hold True or False (datatype)
If and only if all columns are True - then true

MyRow T T T T T T T T T T - Sum in next column is T
MyRow T T T T T T F T T T - Sum in next column is F

Speed is very important. The Recordset for a single row is still open on the Currrent Record since the Update just finished.

Code:
RS_RE_1SegStatusProfiled.Fields("Total") = (RS_RE_1SegStatusProfiled.Fields("RE_1") AND CStr(RS_RE_1SegStatusProfiled.Fields("RE_2") AND (RS_RE_1SegStatusProfiled.Fields("RE_3") ' and so on

My guess is that since the recordset is open to the current record on a local table, it will be efficient to just re-read all of the values and And them together.

Since I have code writing to each record, I could also go through all the extra assignment of a local variable.

View 4 Replies View Related

Queries :: Function To Return Required Value In SQL String

Jan 11, 2015

I have the following function declared however cant get it to work in the sql string..

Code:
Public Function GetSystemID() As String
GetSystemID = fOSUserName
End Function

However cant get it to return the required value in the SQL string..

Code:
DoCmd.RunSQL "INSERT INTO tblLogs (LoginUser, LoginTime, SystemUser) " _
& "VALUES(forms.frmlogin.txtUserID.Value, Now(),GetSystemID)

View 3 Replies View Related

General :: Which Function In Access Return Last Value In A String

Aug 14, 2013

Which function in access return the the last value in a string.

Period 1 Period 2 Period 3
10 20 30

I need the function to return 30.The reason behind this I have different periods for categories and my formula I'm using needs the ending value.

View 1 Replies View Related

Modules & VBA :: Set A Public Variable In Another Database To True

Apr 14, 2015

Is it possible to open another database, set a Public boolean variable in a module?

in db1: Open database by Access.Application, OpenCurrentDatabase, setting obj to db2.CurrentProject, loop through main objects

in db2: Public ByPassCloseVar As Boolean is in a module ModLinkTblReview

I have a process that opens up a database runs through all of its objects to get their properties and values. There is a form that is set up on Unload to close the tool if the variable is set to False. When I open the form to get the properties and their values, then close the form, it closes the database. I would like to set the variable to true in order for the database to stay open.

Thought something like this would work: db2.Modules!ModLinkTblReview.ByPassCloseVar = True

Indicates that method or data member does not exist.

View 5 Replies View Related

General :: String Function To Detect First Alphabetical Character?

Jul 18, 2014

I have a string where alphabetical and numerical characters are mixed up. I need a string function where I can find the index of the first non-numerical character. I tried with the mid function but could not get it to work.

View 13 Replies View Related

Modules & VBA :: Checkbox True When Current Date Is Passed

Dec 3, 2014

I have a form that allows you to search for records and displays all the information for that project, typical stuff.

On this form is a subform with the followings Fields:

Inspection Requested, Inspection Due, Inspection Done, Overdue

When you update inspection requested the inspection due is automatically updated to 30 work days from that day.

Now what I'm trying to do is get the overdue field (it's a yes/no field) to automatically check itself when the current date is passed the inspection due and the inspection done field is blank.

Here's what I have that isn't working:

Code:
Private Sub Form_Current()
If DateAddW([REQUEST], 30) < Date And [INSPECT DONE] = 0 Then
[OVERDUE] = True
End If
End Sub

DateAddW is a UDF that works just fine. I've tried replacing 0 with Null and neither works.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved