Modules & VBA :: Code That Creates A Long String That Works Most Of Time But Sometimes Gets Cut Short
Nov 5, 2014
I have the following code that creates a long string that works most of the time but sometimes gets cut short. I can't understand why it does this. When it cuts the string short it cuts it short in the same place. Everything gets in the string up to/or about the following code '</Practice Name>'This string is needed to upload info to a server.
Code:
dim msg as string
msg = "<?xml version=""1.0"" encoding=""utf-8""?>" & vbCrLf & _
"<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" & vbCrLf & _
" <soap:Body>" & vbCrLf & _
" <UpdateDataForScreens xmlns=""http://mdtoolboxrx.com/"">" & vbCrLf & _
" <PatientObj>" & vbCrLf & _
[code]....
View Replies
ADVERTISEMENT
Dec 9, 2004
Hello again,
I think my subject heading explains it. How would you add or subtract two short time formatted values and produce a short time value? Also, how would you change a short time value into number minutes. (i.e. 5:30 into 330) Thanks!
G
View 1 Replies
View Related
Jun 21, 2013
I want to be able to set text boxes so that if one enters a date and a time it displays in the format "dd/mm/yyyy hh:mm", but if one just enters a date is displays in the format "dd/mm/yyyy". Is this possible?
Stipulating "dd/mm/yyyy hh:mm" means that when you just enter a date it adds "0" values for the time e.g. entering "21/6/13" gives "21/06/2013 00:00".
General Date allows for an optional time, but it means that when you do enter a time it gives you seconds as well "dd/mm/yyyy hh:mm:ss" - and I don't want that.
View 7 Replies
View Related
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
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
This screen shot may help:
View 2 Replies
View Related
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still short time but the format is in AM/PM or medium time.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
View 1 Replies
View Related
Nov 16, 2006
Hi people,
I could do with some help,
I have a combo box which holds a list of times which I have entered in it.
eg. 08:00, 08:30, 09:00 09:30, 10:00
I have set the format of the combo box to be short time 00:00
However, when I move the value that has been selected in the combo box to either an sql query or a text box say...
Me.txtbox1.value = me.combo.value
When the value is transferred I get a long looking time like this 08:30:00
Can anyone tell me why this is happening and how I can get it to just appear as a short time like 08:30
Many Thanks
Jay
View 5 Replies
View Related
Jul 31, 2013
What is the difference between "short text" and "long text"? Both seem to have 255 characters limit. I have Access 2013, and I found a solution, that you can use Memo instead of Short text. But the problem is, that there is no "memo" data type in Access 2013. So that won't work for me.
View 8 Replies
View Related
Sep 12, 2014
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
View 14 Replies
View Related
May 22, 2015
I am trying to add 30 or 45 days to a certain date and insert that date in a table. The field is declared as datetime on the SQLServer and has been tried with all date formats, and none, on the access side.The problem I am encountering is that the date is being inserted as 12:00:00 in the table, no date is being stored.When I print out the SQL statement to the immediate window, the date in question shows the correct date.
Code:
Dim a As Date
Dim b As Date
a = DateAdd("d", 45, Me.DateOfExit)
b = DateAdd("d", 30, Me.DateOfExit)
strSQL = "INSERT INTO MyTable ( No, Name,DueDate, OriginatedBy, ID, Status) " & vbCrLf & _
"VALUES (" & Me.No & ",'" & Me.Name & "', " & b & ", '" & Me.Facilitator & "', " & Me.ID & ", '" & "Required" & "');"
I have tried adding the date add code to the SQL string, but that produced the same result.
View 4 Replies
View Related
Aug 29, 2014
I've got a string variable with a value that could be typically
"ABCD|123|R"
The string needs to be split into its three parts, the pipe symbol being the separator. Then the middle numeric string must be converted back to a long.
I know i could do a loop which identifies each character in turn, but my question is:
Is there a VBA function that can pick out the position in the string of the "|" characters, so i dont need a loop ?
View 3 Replies
View Related
Mar 8, 2006
Let's say I have a query like:
select [x]+[y] as A, [z]*[A] as B from MyTable.
I have found that sometimes this works and sometimes it doesn't, (Like, it works the first time I run it, but later quits working....)so I've gotten into the habit of repeating my calculations and writing it thus instead:
select [x]+[y] as A, [z]*([x]+[y]) as B from MyTable.
However, I now have some query fields that have so many calculations in them that I exceed the string length that Access allows for a query field. So is there a way to make the shorter SQL work consistently?
Any ideas appreciated.
Thanks!
PS: Here's the field I need to shorten:
AdjFcstDay: IIf(((Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))/(First([forecastweeks])*5))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0))<0,0,(Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0)))
View 1 Replies
View Related
Sep 16, 2013
I have a table where I want to break longer strings into three-character strings.
So... for example the data item "Forest," I'd like to generate "FOR" "ORE" "RES" "EST" and write all four to another table, allowing for duplication. Alternately, we could make a list and just count the unique 3-character strings. The end goal here is to measure the frequency of 3-character strings across a set of data.
how to do this with an Access query?
View 1 Replies
View Related
Mar 19, 2008
I am using Access 2007 and trying to set the BackColor property of a text
box on a form using VBA Code in the Form_Open event with code like this:
Me.txtName.BackColor = <some global variable>
Here's the thing: In order to get the correct color numer, I first pick my
desired color from the palette to see its value. In this case, a very bright
red comes up as #ED1C24.
In Access 2003 and earlier, this value would be represented as a long
integer, not a Hex. So, my question is how to get the HEX value converted to a long integer??
The BackColor property is supposed to be a long integer data type, but that is NOT what the design interface is presenting to me!
Why did Microsoft change the BackColor property of form and report objects from long integer representation to Hex?
CONFUSED!!
View 2 Replies
View Related
Apr 14, 2008
I have written a bit of code in a Microsoft Access 97. The code basically creates a Microsoft outlook message. I am trying to add in the message that I want to display in Outlook by using the MyMessage ="" command. But the message I want to add is too long to fit on one line of code - I get to the end of the line and can no longer type any more! I have tried to add the second part of the message to the next line but all I get is an "Expected line number or label or statement or end of statement" error message. I think this might be because access keeps putting a " at the end of the first line - every time I remove it and add the " to the end of the second line it keeps adding it back to the end of the first line!
any ideas would be gratefully received!!!!!
View 2 Replies
View Related
Jan 5, 2008
I'm trying to refine another of my time entries. To enter a Short time (eg 10:35), I have to type in the four digits AND the semi-colon in the middle.
I would like to be able to enter only the four digits with Access making it into the correct format.
I have achieved a similar effect in Excel using code like
TimeVal = Left(Value, 2) & ":" & Right(Value, 2)
I tried writing something similar in the Before_Update Event, but it was rejected!! Any ideas? - Thanks, Andrew.
View 5 Replies
View Related
May 12, 2005
Can someone please help me. I was in the code view of my database and accidentally deleted a few lines of code and now everything i do is coming up with the same error message
Module Not Found
For every form I try to open it does this and even my buttons to close form and to exit system do not work.
Has anybody had this problem before?
Would anybody mind taking a look at it if I sent a zipped version? my email and msn is nicholaseary@hotmail.com
View 2 Replies
View Related
Sep 2, 2004
I have an Excel macro that formats a chart and I have tried copying and adapting it for Access.
The following line is executed OK in Excel :
Select Case ActiveChart.SeriesCollection(Counter).Name
I have changed it to the followig for Access
Select Case Me!DERVGraph.Object.Application.Chart.SeriesCollec tion(Counter).Name
but in Access I get an error 'Object does not support this property or method.
(The space shown in SeriesCollec tion above is a screen bug)
Any ideas?
Thanks
View 8 Replies
View Related
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
Feb 26, 2006
This must have been answered before, but I can't find it by searching so sorry for the dumb question.
I input a time in hours and minutes in a field with a Short Time format. I would like then to convert this to seconds in a field with a number format so that I can total the seconds and then add them to the results of a calculated DateDiff control to get the grand total of time spent on a project.
Any help would be gratefully received.
Cheers
Rob
View 2 Replies
View Related
Apr 2, 2008
I am trying to add hours entered in a short time format. For example 08:15 or 02:55. I also would like it to give a total number of hours beyond 23:00. Here is a table with a Name Column and Hours Column
Name Hours
BETTER,HEATHER 8:00
BETTER,HEATHER 8:00
BETTER,HEATHER 5:41
BETTER,HEATHER 2:35
BETTER,HEATHER 1:15
WILLIAMS,R 2:30
WILLIAMS,R 0:45
WILLIAMS,R 11:20
WILLIAMS,R 8:25
WILLIAMS,R 8:15
WILLIAMS,R 10:20
How do I
A) write a query to add up the hours. I believe Heather would total 25:31 Hours.
B) Show this same total in a report? In my report I had a Name Footer which showed "=Sum([hours])". Will this work once the query is right or will I use the "=Sum([totaltime])"?
Also, is it possible for you to respond in a SQL statement so that I can copy? Thanks.
View 6 Replies
View Related
Jun 22, 2014
i have a problem i have field contains (minutes as number) like 5750 i want to convert thes minutes to short time hh :nn to be 95:50
View 3 Replies
View Related
Feb 12, 2014
How can I convert a number format into a short time format?
View 3 Replies
View Related
May 14, 2013
I have a totals query that displays the sum of what products we ship each day. A process in the system automatically assigns the date/time to a ShippingDate field when an item is marked as despatched.
The totals query allows users to view what products are shipped between a period specified by the user.
The problem I am getting is that when the user inputs dates into fields [txtStart] and [txtEnd], the query fires up but will not display any records as a start time 00:00:00 and end time 23:59:59 was not input.
I don't want users to have to do this but cannot think of a way around it. My criteria code in the query for the shipping dates is
Code:
Between [Forms]![Switchboard]![txtStart] And [Forms]![Switchboard]![txtEnd]
Can I append the start / end time onto the code above somehow or is there another method I can utilise that is probably so glaringly obvious I have missed.
Usually a query allows me manipulate a field property sheet but in this case there is nothing obvious to select like format.
View 2 Replies
View Related
Jun 26, 2015
The task is (1) output an Access query to Excel (2) overwrite that file if it already exists (3) apply specific formatting to the header row and the other rows in Excel. I have cobbled the code together from two sources. The beginning and end are adapted from code on btabdevelopment.com but the large insert in the middle is code I got form a project a former colleague had done. But he's no longer around.
The problem: I click the button and everything works OK. The file is created and formatted just how I want. If I click the button a second time though, it seems to run OK, but when I open the file it is NOT formatted. However, theres another window behind it called Book 1 which has all the data and all the correct formatting it just hasnt been saved. If I click it a third time I get an error message that says Object variable or With block variable not set. Im not even 100% all that is accurate because I have tried it a multitude of ways, closing and re-opening the form, closing and re-opening Access itself, starting with Excel open or closed, never with the destination excel file open though. I dont seem to get exactly the same behaviour any two times. But as far as I can see, if I close and re-open Access, it always works the first time. So I can live with it.
Code:
Private Sub cmdExport_Click()
On Error GoTo Errhandler
Dim rs As DAO.Recordset
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
[code]....
View 8 Replies
View Related
Sep 12, 2013
I have manipulated the duplicate record code found online to suit my purposes. It works for all the other subforms, however this subform has too many fields so the code goes onto the next line. How do I solve this? Online it says using "& _ " but I can't get it to work. There's basically too many fields to fit on one line so I need to continue the code onto the next line
Code with end of line problems highlighted in red:
If Me.[subformEmissionsControlTarget].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblEmissionsControlTarget ( System_ID_No, EmissionsStandard, OtherEmissionsStandard, IntakeThrottle, OBDrequired, OBDVersion, DPFregenmethod, ExhaustTemperature, EGRTemperatures, EGRRates, SteadyStateCycle, SteadyStateEngineeringLimitType, SteadyStateLegislativeLimitSmoke,
[Code] ....
View 2 Replies
View Related
