Modules & VBA :: Converting String To Variables?

Jan 30, 2015

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

View Replies


ADVERTISEMENT

Modules & VBA :: CDate - Converting String Into Date

Nov 12, 2013

I would like to convert 2013.11.13 which is a string into a date. This is what i have so far but it's not working.

2013 year

11 month
13 day of week
Dim strDate As String, dteConvertedDate As Date
strDate = 2013.11.13
dteConvertedDate = CDate(Mid$(strDate, 6, 2) & "/" & Left$(strDate, 4) & "/" & Right$(strDate, 8))
MsgBox (dteConvertedDate)
End Sub

View 7 Replies View Related

Modules & VBA :: SQL Server - Conversion Failed When Converting Date And / Or Time From Character String

Feb 13, 2014

I have two table

1. dbo.period (OpeningDate, ClosingDate)
2. dbo.data (blah blah, doc_date)

I want to create a view as follows

Select doc_date from dbo.data
where doc_date> 'select OpeningDate from dbo.period'

both doc_date and opening date have the same format

but the error will still appear as follows:
"Conversion failed when converting date and / or time from character string."

View 3 Replies View Related

Using RecordSets With Variables (string Issue)

Dec 13, 2005

I'm trying to use a RecordSet to pull data out of an Access DB. I need to compare a field in the DB to a text box in my form. This is what I have so far.


Dim Hardware As Database
Dim Records As Recordset
Dim strSQL As String
Set Hardware = OpenDatabase("c:hardware.mdb")
Serial.SetFocus
strSQL = "SELECT * FROM Computers WHERE Computers.Serial = " & Serial.Text

Set Records = Hardware.OpenRecordset(strSQL)


When I try to run this I get a data type mismatch. Some explanation.
Computers is a table, Computers.Serial is a field in that table. The "Serial" in Serial.Text is a field in my form. You may ask why I'm using the "&" operator instead of just putting it all in the string. The reason is that I cannot figure out how to get VB to treat "Serial.text" as a variable and not a string literal. When I try to put it in single quotes (') I get an error saying the OpenRecordSet call requires an argument, and I'm not providing one, meaning I'm quoting it wrong somehow. Any ideas?

View 2 Replies View Related

Queries :: Global Variables To Be Used In SQL String

Sep 12, 2014

I have a number of global variables set when a user logs into my application which i want to use in an SQL string to record these in a table however im not sure how i go about it i know simply inserting the defined names in the SQL wont work.

Is there a way to define these in global functions instead?

View 1 Replies View Related

Converting Text String To Its ASCII Value?

Mar 18, 2015

I have a string that includes mm/dd/yyyy + person's name. All of this is concatenated and is recognized as a text value. Then I want to convert all of these characters to a number. Since each character has an ASCII code, I would like to find ASCII equivalent.

In Excel, this would involve using something like the CODE ( ) function, but that only gives the numerical equivalent for the first character in the string, but I want all characters at once.

When I try the VAL ( ) function, it returns the first character which is the number of the month.

View 4 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

Converting A Text Date String To A Date For Calculations

Jan 7, 2005

Hi there. I'm just jumping into Access and have the following question. In my Purchase Order Table, I have a date field calcualated as text, i.e. "01/12/04". I need to convert this to a date format in my queries so I can do calculations, i.e. 01/12/04 - 01/05/04 = 7 days. Can someone help me with how to convert this text date to a date format. Sorry if this is an easy question. - AJS

View 4 Replies View Related

Modules & VBA :: Dcount With Date Variables

Jul 11, 2015

Im a relative novice with access VBA, and I'm really struggling with using Dcount with date variable. All I want to do is count if a certain date appears in a table. Here is the extract from my code:

Dim WorkoutDate as Date
Dim datecount as integer

datecount = DCount("[WorkOut Date]", "tblworkoutlogs", "[workout date]= " & WorkoutDate)

Whatever dates are in tblworkoutlogs, datecount is still = 0...I've trawled the net and tried many variations of the code but no success!if I change all the date formats to strings in the code and the tables, it works so I know i'm looking in the correct place.

View 5 Replies View Related

Modules & VBA :: Passing Variables To A Sub Routine

Jan 13, 2014

I have a public sub routine which requires parameters to be passed to it when I call it from an access form. When I try to enter the code to call the sub I get a compile error. I've also tried calling it from another sub in the same module but get the same compile error - see below.

Code:
Sub EmailData(Datafile As String, To_mail As String, CC_mail As String, Subject_mail As String)
'code to use variables passed in
End Sub

[Code]....

View 4 Replies View Related

Modules & VBA :: Possible To Initialize A Function Like Do For Variables?

Jul 15, 2015

Sometimes we need a one-line function to just get the database path or things like that we cannot do on a query or on a Macro Object.

Like:

Function GetDatabasePath() = currentproject.path

possible?

View 5 Replies View Related

Modules & VBA :: Assigning Index To Variables

Sep 11, 2014

How can I assign some type of index to the below variables (data representation) ? I'm using VBA to build a .RFT file for MS Word.

"Line # 1 data"
"Line #2 data"
"Line #3 data"

. . . .

"Line #7 data"
"Line #8 data"
"Line #9"

. . . .

"Line # 22 data"
"Line #23 data"
"Line #24 data"

View 14 Replies View Related

Modules & VBA :: How To Set Up Variables When Access Starts

Oct 9, 2013

I need to set some 'global' variables with default values when my Access 2007 database is loaded. Depending on the user etc these values may be modifed after Access starts but defaults need to be set.

I declared the variables as 'Public' in a Module, then put a function in the same Module (to set the default values) then tried to call the function from an "AutoExec" macro, so it's the first thing that runs when Access loads.The macro throws an error - it can't 'find' the function(?)

View 2 Replies View Related

Modules & VBA :: How To Use Variables In Update Statement

Apr 10, 2014

What is the syntax for using variables in a VBA update statement? I have the following that I want to use to update a record field.

Code:
Dim thisTbl as String
If Answer = vbYes Then
If MedicationInvNo2 <> "" Then
thisTbl = "tblMyMedData"
Else
thisTbl = "tblMedData"

[code]....

I'm getting run-time error '3144' when the database tries to run the SQL. So I'm assuming my syntax is wrong (specifically in the WHERE clause)?

View 3 Replies View Related

Modules & VBA :: Calling Variables From Other Subs

Oct 29, 2014

I have been tasked to create a multi-keyword search form, however, my form isn't working right and only the first record of the table is opened.

Code:
Public Sub txtSearch_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant
Dim i As Integer
Dim IngLen As Long

[Code] .....

These are the codes that I am using for my search form. I have a feeling that I am not calling the variable from the after update portion the right way.

View 14 Replies View Related

Modules & VBA :: Auto-fill On Variables

Apr 9, 2015

I have a Change Management database with several fields, the key fields that I need to talk to work together are as follows:

[Patch] , [CVE] & [Patch Approved]

When opening a form I have I can sort it by the patch number which will then sort out all other CVE's that are not associated with that patch. What I would like to be able to do is approve one patch under the [Patch Approved] field and have it carried through the rest of the filtered CVE's that have the same [Patch] number/ field.

View 1 Replies View Related

Modules & VBA :: Passing Variables And Retrieving Results?

Jul 9, 2014

I wrote this module

Option Compare Database

Option Explicit

Public Function OdometerInput(varodometer As Variant) As Long
Dim varKilometres As Variant
varKilometres = varodometer * 1.609344
OdometerInput = CLng(varKilometres)
End Function

It works fine in the immediate window (although I haven't just fathomed what to do with null values and such) But my question which I am sure will be 'easy when you know' is how do I pass the variable to it from a text box on a form and retrieve the data in another text box on a form.

View 4 Replies View Related

Modules & VBA :: Referring To Forms Objects Using Variables

Sep 30, 2013

I have a simple date stamp that works great in a private sub within a form. (error handling removed for clarity)

Code:

Private Sub btnDateStamp_Click()
' UserInit is global variable
Me!Notes.SetFocus
Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & UserInit & _
" -" & vbCrLf & Me!Notes

[code]...

I am rewriting it as Module function that is Called from various forms, to save space. The function receives the parameters varFormName and varControlName. I wish to write the results of the function back to a memo field on the form.I am stumped at the get go by the need to refer to the Forms controls with a full reference instead of the Me command.

Code:

Function DateStamp(varFormName As String, varMemoName As String)
'varInitials, varFormName, varMemoName are global variables
Forms!varFormName.Controls!varMemoName.SetFocus ' Error here
'Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & varInitials & _

[code]...

how to refer to the forms control's with their full reference, from within the Module's function, the rest will fall into place.

View 3 Replies View Related

Modules & VBA :: Applying Filter In Form With 2 Variables

Jun 6, 2013

I am fairly new to VBA and am trying to apply a filter within a form so that agent records can be filtered based on an agent's name (so that they can only see information that refers to them).

I also want a record to be removed when it has been completed (when this habbens a date completed field will populated) however I do want to see those completed records that refer to the agent that have been completed today.

Here is my code. The 2 filters work on their own howver when I put them together I get a mismatch error.

If Me.txtRole = "Agent" Then
DateCompletedFilter = "(DATECOMPLETED Is Null) Or (DATECOMPLETED = Date())"
AgentFilter = "CASEOWNER ='" & Me.txtName & "'"
DoCmd.ApplyFilter , AgentFilter And DateCompletedFilter
Exit Sub
End If

View 13 Replies View Related

Modules & VBA :: Import Spreadsheet With Form Variables

Jul 18, 2013

I'm looking at importing data into a table from a spreadsheet. The spreadsheet will only contain a single column of data, while the table in Access will have a few more, some of which will would be will provided from the form the user is using to import the data, and some at a later date.

Is there a way to do this? I've found the "DoCmd.TransferSpreadsheet acImport" command, but not sure this fulfills what I need, as I can't see a way to set variable data.

Here is a brief idea of what my Table would look like, where where the data to import would be obtained from

Fields:
PromoID, DiscountID, PromoCode, AddedBy, DateAdded, UsedBy, DateUsed

Spreadsheet
PromoCode

Form:
DiscountID, AddedBy (environ("username") query), Date ( DATE() )

Note, the PromoID is a autonumber.

View 2 Replies View Related

Modules & VBA :: Setting Filter On A Form With Variables

Aug 26, 2014

I am trying to set a filter on a form with VBA using variables and having no success at all. The code I tried last, which seemed to be "close, but no cigar", was:

Code:
Dim sFilterValue As String
Me.FilterOn = False
sBiller = Me.txtbiller.Value
sLastSource = Me.txtbiller.ControlSource
sLastSource = "[" & sLastSource & "]"
sFilterValue = sLastSource & "=" & sBiller
Me.Filter = sFilterValue
Me.FilterOn = True

sLastSource and sBiller are global variables. When I debug this code the sFilterValue is exactly what I would plug in manually and the Me.Filter shows up as "[Field Name] = Filter Value". But it crashes on Me.Filter = sFilterValue. I have tried all sorts of combinations but nothing I've tried works. What is the proper syntax for using variables as filters?

View 2 Replies View Related

Modules & VBA :: Calling A Function And Passing Variables

May 29, 2015

I have never tried passing variables while calling a function so I don't know what the heck I'm doing. I'll give a simplified example of what I'm trying to do. The second variable vRank is reporting properly but the first one vID gets "stuck" on whatever the first item in the listbox is.

Code:
Dim vrt As Variant
Dim upSQL As String
For vrt = 0 To Me.List1.ListCount - 1
If Me.List1.Selected(vrt) = True Then
Call ChangeUp(Me.List1.Column(0, vrt), Me.List1.Column(1, vrt))

[Code] ....

View 3 Replies View Related

Modules & VBA :: Initializing Date And Time Variables

Jul 1, 2014

I am writing a process for a hospital application in the UK

We have a concept of "Takes" which are periods to which patients coming into hospital are assigned to and thus the consultant (specialist) who looks after them.

They are fixed times between 0800-2000 (Day take) and 2000-0800 (+1) Night take.

I have written the following function to try and determine which take a patient should be assigned to based on the time now to power various queries to enable the end user to quickly see "today's day take" and "yesterday's take"

Testing it I seem to not be able to hit the right "Take times" in my output. I suspect it's a problem with the way I'm initiating my variables as Today 0800 and Today 2000

Code:
Function GetTakeStarttime()
Dim StartTime As Long
Dim EndTime As Long
Dim CurrentTime As Long
Dim YestStartTime As Long

[Code] .....

View 2 Replies View Related

Modules & VBA :: Variables As Field Names In Recordset

Mar 12, 2014

I am using Access 2013 under Windows 7. In my database I have a table, tblStock, with field names Module, Component_1, Component_2, etc. up to Component_50. I also have fields Qty_1, Qty_2, etc. up to Qty_50. These field names are not easily changed as they are constantly updated from another database. I want to create a table, tblTempBOM, using VBA by selecting a particular value of Module selected from a combo box on a form, from tblStock and creating a record with the Fields "Module", "Component" and "Qty" for each Component and Qty from 1 to 50. I am trying to use a From... Next... loop to cycle through the Component and Qty fields and store the data in the new table.

Dim strModule As String
Dim strSQL As String
Dim strQty As String
Dim strEye As String
Dim strTest As String

[code]....

This runs to the point where I try to set the value of rcd![StockCode], but the item rcdStock![strTest] has no value.Can I reference the value of the field in the rcdStock Recordset in some other way that would work?

View 5 Replies View Related

Modules & VBA :: Multiple Variables For Insert INTO SQL Statement

Sep 28, 2013

Look at the below SQL 'INSERT INTO' statement ? I'm trying to insert multiple variable values into an 'INSERT INTO' statement. I'm getting the below error message. The code is listed below. I started out with two (2) variables, but will have thirteen to insert into a table. Also, in the code below is the VBA statement to retrieve the variable data. I'm getting the data, but cannot insert the data into the table.

Private Sub Test2_Click()
Dim strSQL As String
Dim strSalesman As String
Dim strContentArea As String
DoCmd.SetWarnings False

[Code] ....

Error
Microsoft Visual Basic popup
Run-time error '3061'

Too few parameters. Expected 1.

View 5 Replies View Related

Modules & VBA :: Checking For Blank Textboxes (Variables)

Mar 2, 2015

I want to check to see if ALL textboxes are blank, then issue a message. My "Select Case" statement and "If" statement does not work. How can I accomplish this ? Below is what I have.

Blank = Null
Select Case Blank
Case strChartOfAccts1,
strChartOfAccts2, strChartOfAccts3, strChartOfAccts4, strChartOfAccts5 & _

[Code] .....

View 5 Replies View Related







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